Finding new records in Latest ID by compared with previous ID

[Anonymous]

Hi Everyone,

I need to find out the new records that were arrived in Latest Time ID by compared with previous Time ID. Below is my sample data set.

I want to set the "NoPrevious" flag in Qlikview By comparing with previous Time Id.

Can any one assist me to get it done.

Logic:

If(TimeID = Previous(TimeID) and MeterCode<> Previous Time ID MeterCode,1,0)

Regards,

Shakila D

[petter · Accepted solution]

I think this should work for you if I understand you right:

You need to combine the two fields into one to be able to make use of the Exists() function to check if previously loaded values exists already in memory. The Previous() function will be of no help as it always refers to the source rows and thus can't combine any source fields at all.

[luismadriz]

Hi Shakila,

Maybe try something like this?

Temp:

LOAD

    Code,

    TimeID,

    "Timestamp",

    "No Previous"

FROM [lib://AttachedFiles/Data set.xlsx]

(ooxml, embedded labels, table is Sheet1);

DATA:

Load *,

     IF(Code=Previous(Code),'Old','New') as Flag,

     IF(Code=Previous(Code),0,1) as NewNoPrevious

Resident Temp

Order by Code,TimeID;

Drop Table Temp;

I hope it helps,

L

[Anonymous]

Thanks for the reply Luis.

But What I need is I want to calculate the NoPrevious column in Excel. My dataset is only have TimeID,Code & Timestamp alone. I want to set the NoPrevious Flag based upon Above logic which i mentioned.

I just provide the sample Output  in Excel along with NoPrevious column.

TimeID	Code	Timestamp
69	1234565	02-10-2017 00:00
69	7896321	02-10-2017 00:00
70	1234565	09-10-2017 00:00
70	7896321	09-10-2017 00:00
70	3456789	09-10-2017 00:00
71	3456789	16-10-2017 00:00
71	7512348	20-10-2017 00:00
72	7896321	20-10-2017 00:00

[Anil_Babu_Samineni]

Can you explain result as well with new column, If you aware?

[luismadriz]

Hi,

That will work based on that data. I'm not using NoPrevious in that logic

Regards,

Luis

[Anonymous]

Thanks Petter, It works as I expected.

[Anonymous]

Petter,

Can you please help me how the below highlighted command will works? Because the td-1 will take different date value, then how it getting matched with TC and set the flag as 1/0.

If(Exists(TC,(TD-1)&'_'&Code),0,1) As NoPrevious;