convert SQL data compare logic to QlikView: Compare Yesterday's data to Today's data and display only changed values

[manideep78]

Hi Community,

I am trying to build a simple QV app just to show what changed from previous day's data to today's data. 

Data source is oracle and currently planning to take 2 days data initially in two separate QVDs and then a new QVD everyday/reload thereafter. (I appreciate any suggestions here.) 

The real data has around 30-35 columns in total.

Data looks like below:

1st day data:

Key	Date	Col1	Col2	Col3
1	15/7/2021	A	P	W
2	15/7/2021	B	Q	X
3	15/7/2021	C	R	Y
4	15/7/2021	D	S	Z

2nd day data:

Key	Date	Col1	Col2	Col3
1	16/7/2021	AA	P	WW
2	16/7/2021	B	QQ	X
3	16/7/2021	C	R	YY
4	16/7/2021	D	S	Z
5	16/7/2021	E	T	ZZ

Expected Output:

First Output table:

Display only New Rows that do not exist in Previous Day

Key	Col1	Col2	Col3
5	E	T	ZZ

Second Output Table:

Show only what changed from yesterday to today and transpose to row level.  This is a kind of Delta/ Changelog table.

Key	Fieldname	OldValue	NewValue
1	Col1	A	AA
1	Col3	W	WW
2	Col2	Q	QQ
3	Col3	Y	YY

I have a logic and solution is currently implemented in SQL. But  this should be in QlikView on a daily basis.

I appreciate any leads/suggestions.

Thank you in advance.

[Vikash]

Hi,

For displaying the first output table-

[1st day data]:

LOAD Key,
Date,
Col1,
Col2,
Col3
FROM Day1_Table;

NoConcatenate

[2nd day data]:

LOAD Key,
Date,
Col1,
Col2,
Col3
FROM Day2_Table where not exists(Key);

Drop Table [1st day data];

VK

[manideep78]

Hi Vikash,

Thank you for the reply. Yes, I got the first part and I'm looking more for second output. I will appreciate any leads.

[manideep78]

Hi All,

I appreciate any pointers to this.