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

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

manideep78 — Fri, 16 Jul 2021 21:31:55 GMT

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.

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

Vikash — Sat, 17 Jul 2021 12:16:29 GMT

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];

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

manideep78 — Tue, 20 Jul 2021 23:52:18 GMT

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.

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

manideep78 — Mon, 26 Jul 2021 12:31:30 GMT

Hi All,

I appreciate any pointers to this.