topic Re: Flag creation in App Development

Flag creation

ksk278074 — Tue, 30 Nov 2021 18:17:33 GMT

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

Re: Flag creation

ksk278074 — Mon, 09 Aug 2021 09:57:20 GMT

Could you please help me on the same?

Re: Flag creation

stevejoyce — Mon, 09 Aug 2021 10:02:18 GMT

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

Re: Flag creation

ksk278074 — Mon, 09 Aug 2021 10:39:48 GMT

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?

Re: Flag creation

stevejoyce — Mon, 09 Aug 2021 10:45:10 GMT

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;

Re: Flag creation

ksk278074 — Mon, 09 Aug 2021 10:45:44 GMT

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.

Re: Flag creation

stevejoyce — Mon, 09 Aug 2021 11:54:14 GMT

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.

Re: Flag creation

NageshSG — Mon, 09 Aug 2021 12:44:20 GMT

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
;

Re: Flag creation

NageshSG — Mon, 09 Aug 2021 13:05:18 GMT

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.

Re: Flag creation

stevejoyce — Mon, 09 Aug 2021 13:43:45 GMT

@NageshSG good point to add! thank you.