Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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;
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
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;
Of course! Use a concatenated key!!
Thank you Manish. That was exactly what I was looking for.