topic Re: Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous wee in App Development

Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous weeks

sarsukanth — Wed, 01 Nov 2023 14:42:02 GMT

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

Re: Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous wee

sarsukanth — Thu, 02 Nov 2023 10:33:04 GMT

Hi All,
anyone has any idea of this Logic, if yes please reply, actually I am waiting for the same.

Thank you.

Re: Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous wee

WaltShpuntoff — Thu, 02 Nov 2023 14:01:01 GMT

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

Re: Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous wee

sarsukanth — Thu, 02 Nov 2023 15:06:43 GMT

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.

Re: Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous wee

WaltShpuntoff — Thu, 02 Nov 2023 15:29:34 GMT

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

Re: Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous wee

sarsukanth — Fri, 03 Nov 2023 12:29:33 GMT

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

if the ID is only 1 time in whole table, then that record is showing as "New", means the count of ID is 1
rest of all showing as "Old", means the count of ID is more than 1

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.

Re: Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous wee

WaltShpuntoff — Mon, 06 Nov 2023 10:18:00 GMT

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;

Re: Need Qlik Sense Script to create a Flag column with "Old" and "New" data items based on the availability of ID in previous wee

a_k_t — Mon, 06 Nov 2023 10:51:35 GMT

@sarsukanth

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