topic Re: load compare two fields in QlikView

load compare two fields

Anonymous — Thu, 19 Feb 2015 19:26:35 GMT

I have a SQL table that contains two fields: name (primary key), and last_update. I also have a QVD containing the same data, plus some flags for further processing; this QVD gets read back in on subsequent runs. For example:

QVD

name	last_update
A	1
B	1
C	1
D	1

Server

name	last_update
A	1
B	2
C	1
D	3
E	2
F	3

I am trying to do a LOAD where the result will be a table:

QVD_updates (i.e. only names in QVD where Server last update is newer than QVD last update).

name	last_update
B	2
D	3

How is this done?

Thanks!

Re: load compare two fields

MK_QSL — Thu, 19 Feb 2015 20:38:49 GMT

First while Storing your QVD add one more field like below

QVD:

Load *, name&last_update as key1 Inline

[

name, last_update

A, 1

B, 1

C, 1

D, 1

];

STORE QVD into QVD.qvd(QVD);

DROP TABLE QVD;

Now

Use as below...

temp:

LOAD

key1

FROM

(qvd);

temp2:

Load COUNT(DISTINCT key1) as TotalKey1 Resident temp;

Let vTotalKey1 = NUM(PEEK('TotalKey1',0,'temp2'));

For i = 0 to $(vTotalKey1)-1

Let vName = PEEK('key1',$(i),'temp');

Let V1 = Left('$(vName)',1);

Let V2 = Right('$(vName)',1);

Server:

Load *,Left('$(Name)',1) Inline

[

name, last_update

A, 1

B, 2

C, 1

D, 3

E, 2

F, 3

] Where name = '$(V1)' and last_update > '$(V2)';

Next;

Drop Table temp;

Drop Table temp2;

Re: load compare two fields

Anonymous — Fri, 20 Feb 2015 19:39:33 GMT

Of course! Use a concatenated key!!

Thank you Manish. That was exactly what I was looking for.