How to create a flag based on blocking date

[jim_chan]

Hi all the master!

I have a issue here. i got a data from user, generated by system. 

My request is - how to create a Block Indicator Flag - X, based on 2 block dates column - a from & to date, like the screenshot below.

so actually, this camp ID 1234, for year 2020, it closes from 1/5/2020 - 31/12/2020. but something wrong with the system, the system generated indicator(BLOCKED_IND), added in late, so the X only start on 13/09/2020.

So for now, i need to create a new field(Flag), that this new indicator flag, will flag from date 1/5/2020 - 31/12/2020. so i can use this new flag , instead from the system.

I have prepared an sample excel as well - 2 sheets, ISSUE and EXPECTED.

I will be working in qlik sense(QVF) file, i dont have QLIKVIEW qvw license. This has to be done in loading script.

ISSUE

EXPECTED(samples screenshot) , please view the excel.

Please help !

Rgds

Jim

[MayilVahanan]

Hi Jim,

Its looks like, similar concept. PFR

https://community.qlik.com/t5/New-to-Qlik-Sense/Filter-by-month-two-dates-columns-that-are-on-the-sa...

[jim_chan]

hey there, 

so does it means - 

1)  generate dates using start date 1/5/2020 and from date 31/12/2020.

2)  join back to the existing table so can get back full correct indicator?

Rgds

jim

[vinieme12]

as below

Main:
LOAD
CAMP_ID,
CAMP_START_DAY,
BLOCKED_IND,
BLOCKED_FRM_DATE,
BLOCKED_TO_DATE
FROM [lib://AttachedFiles/test1.xlsx]
(ooxml, embedded labels, table is ISSUE);

Left Join(Main)
Load
CAMP_ID
,date(from+Iterno()-1) as CAMP_START_DAY
,'X' as BLOCKED_IND_new
While from+Iterno()-1 <= to
;
LOAD
CAMP_ID,
max(BLOCKED_FRM_DATE) as from,
max(BLOCKED_TO_DATE) as to
Resident Main
Group by
CAMP_ID;

Drop field BLOCKED_IND;
Rename field BLOCKED_IND_new to BLOCKED_IND;

exit Script;

[jim_chan]

Dear Vinieme12,

Brilliant work!! so much thank you i have to say to u. it worked. MY BAD, i missed out 1 more requirement, user needs to list out the block from and to date as well, instead of leaving it empty with the new flag. like this. 

What else need to be added, Vinieme12?

Rgds

Jim

[vinieme12]

refer highlighted changes

Main:
LOAD
CAMP_ID,
CAMP_START_DAY,
BLOCKED_IND,
BLOCKED_FRM_DATE,
BLOCKED_TO_DATE
FROM [lib://AttachedFiles/test1.xlsx]
(ooxml, embedded labels, table is ISSUE);

Left Join(Main)
Load
CAMP_ID

,date(from) as from
,date(to) as to
,date(from+Iterno()-1) as CAMP_START_DAY
,'X' as BLOCKED_IND_new
While from+Iterno()-1 <= to
;
LOAD
CAMP_ID

,date(from) as from
,date(to) as to

max(BLOCKED_FRM_DATE) as from,
max(BLOCKED_TO_DATE) as to
Resident Main
Group by
CAMP_ID;

Drop fields BLOCKED_IND,BLOCKED_FRM_DATE,BLOCKED_TO_DATE;
Rename field BLOCKED_IND_new to BLOCKED_IND;

Rename field from to BLOCKED_FRM_DATE;

Rename field to to BLOCKED_TO_DATE;

exit Script;

[jim_chan]

hit on error.

Rgds 

Jim

[jim_chan]

i think i might have solved it Vinieme12, script is a below. is it correct as per what were you trying to quote to me?

Main:
LOAD
CAMP_ID,
CAMP_START_DAY,
BLOCKED_IND,
BLOCKED_FRM_DATE,
BLOCKED_TO_DATE
FROM [lib://AttachedFiles/test1.xlsx]
(ooxml, embedded labels, table is ISSUE);

Left Join(Main)
Load
CAMP_ID
,date(from+Iterno()-1) as CAMP_START_DAY
,date(from) as from,
date(to) as to,
'X' as BLOCKED_IND_new
While from+Iterno()-1 <= to;

LOAD
CAMP_ID,
max(BLOCKED_FRM_DATE) as from,
max(BLOCKED_TO_DATE) as to
Resident Main
Group by
CAMP_ID;

Drop fields BLOCKED_IND,BLOCKED_FRM_DATE,BLOCKED_TO_DATE;
Rename field BLOCKED_IND_new to BLOCKED_IND;

Rename field from to BLOCKED_FRM_DATE;

Rename field to to BLOCKED_TO_DATE;

exit Script;

[vinieme12 · Accepted solution]

sorry my bad;

I edited incorrectly, they should be in the preceding load 

Main:
LOAD
CAMP_ID,
CAMP_START_DAY,
BLOCKED_IND,
BLOCKED_FRM_DATE,
BLOCKED_TO_DATE
FROM [lib://AttachedFiles/test1.xlsx]
(ooxml, embedded labels, table is ISSUE);

Left Join(Main)
Load
CAMP_ID
,date(from_date) as from_date
,date(to_date) as to_date
,date(from_date+Iterno()-1) as CAMP_START_DAY
,'X' as BLOCKED_IND_new
While from_date+Iterno()-1 <= to_date
;
LOAD
CAMP_ID,
max(BLOCKED_FRM_DATE) as from_date,
max(BLOCKED_TO_DATE) as to_date
Resident Main
Group by
CAMP_ID;

Drop fields BLOCKED_IND,BLOCKED_FRM_DATE,BLOCKED_TO_DATE;
Rename field BLOCKED_IND_new to BLOCKED_IND;

Rename field from_date to BLOCKED_FRM_DATE;

Rename field to_date to BLOCKED_TO_DATE;

[vinieme12]

kindly close the thread by accepting a response as solution