Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I want to create the flag based on the 3 fileds.
1.creation date ,region and ID
If i have multiple created dates for the same region and same ID then my flag will be Y else N
Please hlep me how to create the flag for the same.
Thanks in advance.
Thanks,Siva
Could you please help me on the same?
//dummy data
tbl:
load * inline [
id, creation date, region
123, 1/1/2021, abc
123, 1/3/2021, abc
780, 1/3/2021, dec
]
;
//preceding load:
//first count creation dates for each id and region
//second left join to original table as a flag if > 1
left join (tbl)
load
id
,region
,if(count_creationdate_by_id_region>1, 'Y', 'N') as flag_multiple_create_dates_by_id_region
;
load distinct
id
,region
,count(distinct [creation date]) as count_creationdate_by_id_region
resident tbl
group by id, region
;
HI Stevejoyce,
Thanks for the response.
I have tried with your approach but did not get the data for flag.
Could you please explain bit clear?
so let's get rid of the preceeding load for now so you can step through it and see where it's not matching what it should:
1st step is to count distinct creation dates grouped by id & region combination.
2nd is taking this count and assigning Y when >1 else N
3rd is joining that back to your original table.
//Get count of creation dates for each id+region
tempTbl:
load distinct
id
,region
,count(distinct [creation date]) as count_creationdate_by_id_region
resident tbl
group by id, region
;
//exit script
//is this correct? if so...
//create your flag logic
temp2Tbl:
load
id
,region
,if(count_creationdate_by_id_region>1, 'Y', 'N') as flag_multiple_create_dates_by_id_region
;
resident tempTbl;
drop table tempTbl;
//is this correct? if so then...
left join (tbl)
load *
resident temp2Tbl;
drop table temp2Tbl;
In addition to that, i am attaching the sample data file.
for the file need to create the flag if you have multiple created dates for id and region.
Can you explain your issue? group by id, region is what is creating the flag based on id and region. Not sure what i need to use the sample xlsx file for.
I tested the script against the xlsx file. Works as expected to me. If you can explain what you've done and what is not working.
This works!
Another option can be as below in scenarios where the data set is very large:
tbl:
LOAD
autonumber(ID&Region) as IDRegnKey,
ID,
Region,
[Created date],
[closed date]
FROM
Test.xlsx
(ooxml, embedded labels, table is Sheet1);
left Join (tbl) load
IDRegnKey,
if(CntCreateDt>1, 'Y', 'N') as Flag_IDRegn;
Load
IDRegnKey,
count([Created date]) as CntCreateDt
Resident tbl
group by
IDRegnKey
;
Adding on.. just a word of caution!
When you aggregate on date fields containing date/time parts, better to use functions like floor or datestart to avoid unexpected results.
@NageshSG good point to add! thank you.