Hello
I have some issues with Defining Fields from Splitted Raw Data within an Event. Sample Events, Code used to split Raw Event, Output of Splitted Data, and My Issues are given below :
Raw Events:
DAS7CNB_L_0__20210630-23574912_5827.html@^@^2021/06/30@^@^23:57:49@^@^DAS7CNB@^@^select "tin","payer_tin","min"(case when "state" in( 'AA','AE','AP','AS','FM','GU','MH','MP','PR','PW','VI' ) then 1 else 0 end) as "f1065k1nonus","max"(case when "state" in( 'WA','OR','CA','AK','HI','MT','ID','WY','NV','UT','CO','AZ','NM' ) then 1 when "state" in( 'ND','MN','SD','IA','NE','KS','MO','WI','IL','IN','MI','OH' ) then 2 when "state" in( 'NY','PA','NJ','NH','VT','ME','MA','RI','CT' ) then 3 when "state" in( 'TX','OK','AR','LA','KY','TN','MS','AL','WV','DE','MD','VA','NC','DC','SC','GA','FL' ) then 4 when "state" in( 'AA','AE','AP','AS','FM','GU','MH','MP','PR','PW','VI' ) then 5 else 0 end) as "f1065k1maxdistoff","max"("interest") as "interest_f1065_k1","max"("guarpaymt") as "guarpaymt_f1065_k1","max"("ord_inc") as "ord_inc_f1065_k1","max"("othrental") as "othrental_f1065_k1","max"("realestate") as "realestate_f1065_k1","max"("royalties") as "royalties_f1065_k1","max"("section179") as "section179_f1065_k1" into #TEMP9A from "irmf_f1065_k1" where "tax_yr" = 2016 and "tin" > 0 and "tin_typ" in( 0,1,2,3 ) group by "tin","payer_tin"@^@^|DAS7CNB.#TEMP9A|cdwsa.IRMF_F1065_K1@^@^
My SQL Command:
eval SQLField=split(_raw,"@^@^")| table SQLField
Output of Splitted Data:
DAS7CNB_L_0__20210630-23574912_5827.html
2021/06/30
23:57:49
DAS7CNB
select "a"."basetin","w2nonus","w2maxdistoff","ssanonus","ssamaxdistoff","f1099rnonus","f1099rmaxdistoff","f1099miscnonus","f1099miscmaxdistoff","f1099gnonus","f1099gmaxdistoff","f1099intnonus","f1099intmaxdistoff","f1099oidnonus","f1099oidmaxdistoff","f1041k1nonus","f1041k1maxdistoff","f1065k1nonus","f1065k1maxdistoff","wages_w2","allocated_tips_w2","medicare_wages_w2","taxable_fica_tips_w2","WITHHLDG_w2","pens_annties_f1099_ssa_rrb","withhldg_f1099_ssa_rrb","gross_distrib_f1099r","taxable_amt_f1099r","WITHHLDG_f1099r","non_emp_compensation_f1099misc","othincome_f1099misc","rents_f1099misc","royalties_f1099misc","crop_insurance_f1099misc","WITHHLDG_f1099misc","taxbl_grant_f1099g","UNEMP_COMP_f1099g","prior_refnd_f1099g","agr_subsds_f1099g","atta_pymnt_f1099g","WITHHLDG_f1099g","interest_f1099int","savings_bonds_f1099int","WITHHLDG_f1099int","interest_f1099oid","withhldg_f1099oid","interest_f1041_k1","bus_inc_f1041_k1","net_rental_f1041_k1","oth_prtflo_f1041_k1","oth_rental_f1041_k1","interest_f1065_k1","guarpaymt_f1065_k1","ord_inc_f1065_k1","othrental_f1065_k1","realestate_f1065_k1","royalties_f1065_k1","section179_f1065_k1" into #TEMP9 from(select "basetin","w2nonus","w2maxdistoff","ssanonus","ssamaxdistoff","f1099rnonus","f1099rmaxdistoff","f1099miscnonus","f1099miscmaxdistoff","f1099gnonus","f1099gmaxdistoff","f1099intnonus","f1099intmaxdistoff","f1099oidnonus","f1099oidmaxdistoff","f1041k1nonus","f1041k1maxdistoff","wages_w2","allocated_tips_w2","medicare_wages_w2","taxable_fica_tips_w2","WITHHLDG_w2","pens_annties_f1099_ssa_rrb","withhldg_f1099_ssa_rrb","gross_distrib_f1099r","taxable_amt_f1099r","WITHHLDG_f1099r","non_emp_compensation_f1099misc","othincome_f1099misc","rents_f1099misc","royalties_f1099misc","crop_insurance_f1099misc","WITHHLDG_f1099misc","taxbl_grant_f1099g","UNEMP_COMP_f1099g","prior_refnd_f1099g","agr_subsds_f1099g","atta_pymnt_f1099g","WITHHLDG_f1099g","interest_f1099int","savings_bonds_f1099int","WITHHLDG_f1099int","interest_f1099oid","withhldg_f1099oid","interest_f1041_k1","bus_inc_f1041_k1","net_rental_f1041_k1","oth_prtflo_f1041_k1","oth_rental_f1041_k1" from #TEMP8) as "A" left outer join(select "tin","min"(case when "f1065k1nonus" = 1 then 1 else 0 end) as "f1065k1nonus","max"(case when "f1065k1maxdistoff" = 1 then 1 when "f1065k1maxdistoff" = 2 then 2 when "f1065k1maxdistoff" = 3 then 3 when "f1065k1maxdistoff" = 4 then 4 when "f1065k1maxdistoff" = 5 then 5 else 0 end) as "f1065k1maxdistoff","sum"("interest_f1065_k1") as "interest_f1065_k1","sum"("guarpaymt_f1065_k1") as "guarpaymt_f1065_k1","sum"("ord_inc_f1065_k1") as "ord_inc_f1065_k1","sum"("othrental_f1065_k1") as "othrental_f1065_k1","sum"("realestate_f1065_k1") as "realestate_f1065_k1","sum"("royalties_f1065_k1") as "royalties_f1065_k1","sum"("section179_f1065_k1") as "section179_f1065_k1" from #TEMP9a group by "tin") as "B" on "a"."basetin" = "b"."tin" DAS7CNB.#TEMP9
DAS7CNB.#TEMP9A|cdsawsa.IRMF_F1065_K1
My Issues:
It splitted as expected.
But, I have some issues with defining text (please see the text in Bold right above My Issues:) values DAS7CNB.#TEMP9A as ID_DataFile and cdsawsa.IRMF_F1065_K1 as ID_DataTempFile
Thank you.....any help will be highly appreciated.
If there is no trailing pipe you could index from the end
| eval datafiles=split(mvindex(SQLField,mvcount(SQLField)-2),"|")
| eval ID_DataFile=mvindex(datafiles,mvcount(datafiles)-2)
| eval ID_DataTempFile=mvindex(datafiles,mvcount(datafiles)-1)
| eval datafiles=split(mvindex(SQLField,mvcount(SQLField)-2),"|")
| eval ID_DataFile=mvindex(datafiles,1)
| eval ID_DataTempFile=mvindex(datafiles,2)
Thank you so much, appreciated your support.
It's working as expected when I have 2 Pipes "|" (as I mentioned)...but, some cases I found data is with one 1 Pipe "|"
For Example ......payer_tin"@^@^DAS7CNB.#TEMP9A|cdwsa.IRMF_F1065_K1@^@^ ....in that case First Value for ID_DataFile Field is missing.... Is there anything we can do so that I can have both values in both conditions....
like
payer_tin"@^@^|DAS7CNB.#TEMP9A|cdwsa.IRMF_F1065_K1@^@^ (with 2 Pipes)
AND
payer_tin"@^@^DAS7CNB.#TEMP9A|cdwsa.IRMF_F1065_K1@^@^ (with 1 Pipe)
Thank you so much again.
If there is no trailing pipe you could index from the end
| eval datafiles=split(mvindex(SQLField,mvcount(SQLField)-2),"|")
| eval ID_DataFile=mvindex(datafiles,mvcount(datafiles)-2)
| eval ID_DataTempFile=mvindex(datafiles,mvcount(datafiles)-1)
Yes, it's working as expected. Thank you so much, appreciated you support.
Are there any ways I can include these codes into Extraction/Transformation (please see screenshot below) option and extract as fields from the Add New Field Under Field Extractions. Thank you so much again.