Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous weeks.
I have "Week" and "REC_ID" fields in the source data, now I need to create "Status" column newly in Qlik Sense Script.
The logic is, for example, if we take REC5041062815, it is "New" at the 01/07/2022 week but the same is "Old" in coming weeks.
Please let me know if any queries.
Advance Thank you for your support.
Week | REC_ID | Status |
01/07/2022 | REC5041062815 | New |
01/14/2022 | REC5040286357 | New |
01/21/2022 | REC5040286357 | Old |
01/21/2022 | REC5040338452 | New |
01/28/2022 | REC5040004839 | New |
01/28/2022 | REC5040286357 | Old |
01/28/2022 | REC5040338452 | Old |
01/28/2022 | REC5041062815 | Old |
02/04/2022 | REC5040005583 | New |
02/04/2022 | REC5040005813 | New |
02/04/2022 | REC5040338452 | Old |
Hi All,
anyone has any idea of this Logic, if yes please reply, actually I am waiting for the same.
Thank you.
How are you loading your data?
If it is an incremental load where you are just adding records you could do something like this:
1) Load the old data
2) when you load the new data, have a line in your code like this
If(Exists(REC_ID), 'Old', 'New') as tempStatus;
3) Now you need to backfill the missing values
NewTable:
Noconatenate
Load
*,
If(IsNull(tmpStatus, 'Old', tmpStatus) as Status,
....
resident OldTable;
Drop Field tmpStatus;
Drop Table OldTable;
that what you are looking for?
-ws
Hi WaltShpuntoff,
Thank you for your response.
I am not loading the data incrementally, I am loading the data at a time with single table.
in this case, can you please suggest some logic.
Thank you.
How about something like this?
NewTable:
NoConcatenate
Load
REC_ID,
Count(Week) as RecCount
resident SourceTable
Group by REC_ID;
Left join (SourceTable)
Load REC_ID, RecCount
Resident NewTable;
Drop table NewTable;
FinalTable:
Noconatenate
Load
*,
If (RecCount=1, 'New', 'Old') as RecordStatus
resident SourceTable;
drop table SourceTable;
drop field RecCount;
- ws
Hi WaltShpuntoff,
Thank you again for your response.
I have tried with the above script, but it is giving the count of REC_ID as
so means it is just counting of all the IDs.
now we need the script to give the record count as incrementally based on each ID.
please let me know if you have that logic.
Thank you again for your extended support.
I misunderstood your requirements. (It happens now and then)
Try something like this:
NewTable:
noconcatenate
Load
REC_ID,
Week,
If(REC_ID=Previous(REC_ID) OR IsNull(Previous(REC_ID)), 'New', 'Old') as RecordStatus
Resident OldTable
Order by REC_ID, Week
;
drop table OldTable;
I have tried this approach and it works for me. You can try this out.
Load Week, REC_ID, if(Exists(REC_ID),'Old','New') as Status Inline [
Week, REC_ID
01/07/2022, REC5041062815
01/14/2022, REC5040286357
01/21/2022, REC5040286357
01/21/2022, REC5040338452
01/28/2022, REC5040004839
01/28/2022, REC5040286357
01/28/2022, REC5040338452
01/28/2022, REC5041062815
02/04/2022, REC5040005583
02/04/2022, REC5040005813
02/04/2022, REC5040338452
];