Flag creation

[ksk278074]

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

[ksk278074]

Could you please help me on the same?

[stevejoyce]

//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
;

[ksk278074]

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?

[stevejoyce]

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;

[ksk278074]

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.

[stevejoyce]

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.

[NageshSG]

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
;

[NageshSG]

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.

[stevejoyce]

@NageshSG good point to add! thank you.