Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
ksk278074
Contributor III
Contributor III

Flag creation

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

 

Labels (1)
9 Replies
ksk278074
Contributor III
Contributor III
Author

Could you please help me on the same?

stevejoyce
Specialist II
Specialist II


//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
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

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
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

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
Partner - Contributor III
Partner - Contributor III

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
Partner - Contributor III
Partner - Contributor III

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
Specialist II
Specialist II

@NageshSG good point to add! thank you.