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]

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

[sarsukanth]

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

Thank you.

[WaltShpuntoff]

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

[sarsukanth]

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.

[WaltShpuntoff]

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

[sarsukanth]

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.

[WaltShpuntoff]

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;

[a_k_t]

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