two inputlookup files sum of fields

[surekhasplunk]

|inputlookup file1.csv |search "field1"="RUN"

|eval Aperm = if( 'Perm / Cont' = "Permanent",FTE,0)
|eval Acont=if( 'Permanent / FTC' = "Cont",FTE,0)

|eval Bperm = if('Perm / Cont' = "Permanent",FTE,0)

|eval Cperm = if('Perm / Cont' = "Permanent",FTE,0)

|eval Bont=if('Permanent / FTC' = "Cont",FTE,0)

|eval Ccont=if('Permanent / FTC' = "Cont",FTE,0)
| stats sum(Aperm) as APERM sum(Acont) as ACONT sum(Bperm) as BPERM sum(Bcont) as BCONT sum(Cperm) as CPERM sum(Ccont) as CCONT BY "Area"
|APPENDCOLS[|inputlookup TechIndia.csv |search "SubDivision 5"=* "SubDivision 2" = "RUN"

|eval Dperm = if('Perm / Cont' = "Permanent",FTE,0)
|eval Dcont=if('Permanent / FTC' = "Cont",FTE,0) |stats sum(Dperm) as DERM sum(Dcont) as DCONT BY "Region" |fields DPERM DCONT]

In my output currently am getting all the required columns but unfortunately the DPERM and DCONT values are incorrect against the Area or Region. They dont show the corresponding values against the area.

The output should be
Area /Region | APERM | BPERM | CPERM |DPERM |ACONT|BCONT|CCONT|DCONT|Total PERM| Total CONT|

[kamlesh_vaghela]

HI @surekhasplunk,

Can you please try the search ?

| inputlookup file1.csv 
| search "field1"="RUN" 
| eval Aperm = if( 'Perm / Cont' = "Permanent",FTE,0) 
| eval Acont = if( 'Permanent / FTC' = "Cont",FTE,0) 
| eval Bperm = if('Perm / Cont' = "Permanent",FTE,0) 
| eval Cperm = if('Perm / Cont' = "Permanent",FTE,0) 
| eval Bcont = if('Permanent / FTC' = "Cont",FTE,0) 
| eval Ccont = if('Permanent / FTC' = "Cont",FTE,0) 
| stats sum(Aperm) as APERM sum(Acont) as ACONT sum(Bperm) as BPERM sum(Bcont) as BCONT sum(Cperm) as CPERM sum(Ccont) as CCONT BY "Area" | rename "Area" as "Area /Region"
| append [
| inputlookup TechIndia.csv 
| search "SubDivision 5"=* "SubDivision 2" = "RUN" 
| eval Dperm = if('Perm / Cont' = "Permanent",FTE,0) 
| eval Dcont=if('Permanent / FTC' = "Cont",FTE,0) 
| stats sum(Dperm) as DPERM sum(Dcont) as DCONT BY "Region" 
| rename "Region" as "Area /Region"
] | stats values(APERM) as APERM values(BPERM) as BPERM values(CPERM) as CPERM values(DPERM) as DPERM values(ACONT) as ACONT values(BCONT) as BCONT values(CCONT) as CCONT values(DCONT) as DCONT by "Area /Region"
| eval "Total PERM"=APERM + BPERM + CPERM + DPERM
| eval "Total Cont"=ACONT + BCONT + CCONT + DCONT 
| table "Area /Region" "*PERM" "*CONT" "Total PERM" "Total CONT"

Thanks

[niketn]

@surekhasplunk, can you add fields with mocked data for both lookup files and current output with sample data explaining the issue and expected output sample data

[thiagodede]

In your case, on the second inputlookup you should rename the field REGION to AREA and instead use the APPENDCOLS use the JOIN by the field AREA to make the correct relation between the two searchs.

YOUR FIRST SEARCH
|JOIN Area [|inputlookup TechIndia.csv |search "SubDivision 5"=* "SubDivision 2" = "RUN" 
|eval Dperm = if('Perm / Cont' = "Permanent",FTE,0)
|eval Dcont=if('Permanent / FTC' = "Cont",FTE,0) |stats sum(Dperm) as DERM sum(Dcont) as DCONT BY "Region" |rename "Region" as "Area"]

[thiagodede]

To obtain the output you requested, you can add a fields command to ordenate the field in the order you desire and the ADDTOTAL command to create the 2 new columns with the SUM of the colums you want.

|fields Area APERM BPERM CPERM DPERM ACONT BCONT CCONT DCONT
|addtotals col=f fieldname="Total PERM"  APERM BPERM CPERM DPERM
|addtotals col=f fieldname="Total CONT"  ACONT BCONT CCONT DCONT

[DennisFFM]

Hi @surekhasplunk, is this the exact search you're using?

If so, there is a typo in the last line:

|eval Dcont=if('Permanent / FTC' = "Cont",FTE,0) |stats sum(Dperm) as **DERM** sum(Dcont) as DCONT BY "Region" |fields DPERM DCONT]

I think it should be DPERM.

Dennis

[surekhasplunk]

thats a mocked query so might b some typos but my problem is separately two quereis are returning me values but when i merge them together the inner query fields aren;t visible outside and when visible are coming in wrong order with the area