Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai33
Partner
Partner

Dates based on other fields

Hi all,

I've my source data in the following format

IDDateLoc
A12301.10.2020ABC
B24503.10.2020ABC
A12311.10.2020ABC
A12312.10.2020456
A12301.11.2020FGT
A12310.11.2020789
A12315.11.2020DEF

 

I would like to generate the following dates for each ID in each LOC based on the LOC value. My expected data would be as follows:

IDValid From DateValid To date
A12301.10.202011.10.2020
B24503.10.2020Today
A12301.11.202009.11.2020
A12315.11.2020Today

 

Basically, a ID has to have Valid from and To dates until the LOC is not an Numeric value, if there is no new record for a ID then Today() has to be considered as the Vaild To Date.

Please, let me know if i'm not clear enough.

Regards

Sai 

Labels (1)
3 Replies
Anonymous
Not applicable

IF(LOC='ABC', Aggr(max(Date),ID),

      If(LOC='ABC' and Aggr(max(Date),ID)=Date, Today()) AS Valid To date.

In script use Group by for ID

PrashantSangle

Hi,

 

Try below code

 

Temp:
Load ID,Date(Date#(Date,'DD.MM.YYYY')) as Date,Loc inline [
ID, Date, Loc
A123, 01.10.2020, ABC
B245, 03.10.2020, ABC
A123, 11.10.2020, ABC
A123, 12.10.2020, 456
A123, 01.11.2020, FGT
A123, 10.11.2020, 789
A123, 15.11.2020, DEF
];

NoConcatenate
Temp1:
Load ID,
max(Date) as Valid_To_Date,
Loc
Resident Temp
Group by ID, Loc
;

Left join
Load ID,
min(Date) as Valid_From_Date,
Loc
Resident Temp
Group by ID, Loc
;

NoConcatenate
Final:
Load ID,
Date(Valid_From_Date) as Valid_From_Date,
if(Valid_From_Date = Valid_To_Date,'Today',Date(Valid_To_Date)) as Valid_To_Date,
Loc
Resident Temp1;

Drop Tables Temp, Temp1; 

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
MayilVahanan

Hi @Sai33 

May be Try like this


Res:
LOAD *, RowNo() as Rec INLINE [
ID, Date, Loc
A123, 01.10.2020, ABC
B245, 03.10.2020, ABC
A123, 11.10.2020, ABC
A123, 12.10.2020, 456
A123, 01.11.2020, FGT
A123, 10.11.2020, 789
A123, 15.11.2020, DEF
];

Final:
Load *, If(ID <> Previous(ID), Date(TOday()),If(ID = Previous(ID) and Previous(LocFlagNum)=1, Date(Peek(Date1)-1), if(ID=Previous(ID) and LocFlagNum = 0, Peek(Date1), Date))) as Date1;
Load *, if(IsNum(Loc), 1,0) as LocFlagNum, ID&'|'&Loc as Key Resident Res order by ID, Rec desc, Loc;

ValidDate:
Load Key, Date(Min(Date)) as ValidFromDate, Date(Max(Date1)) as ValidToDate Resident Final where LocFlagNum = 0 Group by Key;

DROP Table Res;

EXIT SCRIPT;

Output:

MayilVahanan_0-1606452675309.png

 

Highlighted record will update based on Today Date.

Thanks & Regards,
Mayil Vahanan R