topic Re: Finding new records in Latest ID by compared with previous ID in QlikView

Finding new records in Latest ID by compared with previous ID

Anonymous — Mon, 11 Dec 2017 19:57:23 GMT

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

Re: Finding new records in Latest ID by compared with previous ID

petter — Mon, 11 Dec 2017 23:40:35 GMT

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.

Re: Finding new records in Latest ID by compared with previous ID

luismadriz — Mon, 11 Dec 2017 23:49:36 GMT

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,

Re: Finding new records in Latest ID by compared with previous ID

Anonymous — Tue, 12 Dec 2017 05:20:16 GMT

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

Re: Finding new records in Latest ID by compared with previous ID

Anil_Babu_Samineni — Tue, 12 Dec 2017 05:25:39 GMT

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

Re: Finding new records in Latest ID by compared with previous ID

luismadriz — Tue, 12 Dec 2017 05:29:14 GMT

Hi,

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

Regards,

Luis

Re: Finding new records in Latest ID by compared with previous ID

Anonymous — Wed, 13 Dec 2017 22:58:09 GMT

Thanks Petter, It works as I expected.

Re: Finding new records in Latest ID by compared with previous ID

Anonymous — Wed, 13 Dec 2017 23:04:28 GMT

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;