topic Rows comparision from Backend in App Development

Rows comparision from Backend

rasoolshaik — Thu, 26 Dec 2024 16:44:07 GMT

Hello everyone,

I am having 2 tables. one is for daily reload and second one is incremental qvd for daily qvd.
Here we are having 5 fields, we are capturing daily data,if there is any change in 5 fields then those records are storing into incremental qvd .
This process we are doing by AutoNumber condition.
Now, If we want to add any new field to this list, instead of adding that field in If condition manually
is there any way to check if any change is happened in the records.

Daily Qvd have these records:

Item Name	Dim1	Dim2	Dim3	Volity Code	Last Updated
123	10	15	7	C	12/26/2024
124	5	6	10	B	12/24/2024

Incremental Qvd is having below records:

Item Name	Dim1	Dim2	Dim3	Volity Code	Last Updated
123	10	15	7	A	12/24/2024
124	5	6	10	B	12/24/2024

My result would be like

Item Name	Dim1	Dim2	Dim3	Volity Code	Last Updated
123	10	15	7	A	12/24/2024
124	5	6	10	B	12/24/2024
123	10	15	7	C	12/26/2024

We are using AutoNumber(Dim1&Dim2&Dim3&Volity Code) as KEY

we have added new field for comparison and field name is Weight

Item Name	Dim1	Dim2	Dim3	Volity Code	weight	Last Updated
123	10	15	7	A	30	12/26/2024
124	5	6	10	B	20	12/24/2024

AutoNumber(Dim1&Dim2&Dim3&Volity Code&weight) as KEY

Instead of checking field values manually by using AutoNumber whether it is changed or not is there anyway to automate or direct function to compare between rows and identify which is having duplicate records and which is having distinct records.

Thank you in Advance

Re: Rows comparision from Backend

rwunderlich — Thu, 26 Dec 2024 23:50:31 GMT

Daily:
Load * From Daily.qvd (qvd);

Concatenate (Daily)
Load DISTINCT * From Incremental.qvd (qvd);

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Rows comparision from Backend

rasoolshaik — Mon, 30 Dec 2024 17:46:45 GMT

Hi rwunderlich thanks for your response.

In above scenario, in some cases all fields will remain same but Last Updated is getting changed. In this case how to do the same.

For example: in below scenario we need to get only Min Date. Without using any Group by or concatenating fields. is there any way to achieve this

Item Name	Dim1	Dim2	Dim3	Volity Code	weight	Last Updated
123	10	15	7	A	30	12/26/2024
123	10	15	7	A	30	12/24/2024

Re: Rows comparision from Backend

rwunderlich — Wed, 01 Jan 2025 16:46:33 GMT

"Without using any Group by or concatenating fields". I don't know how you would do it with this restriction, but you can automate the concatenate for any number of fields like this:

Data:
LOAD * Inline [
Item Name Dim1 Dim2 Dim3 Volity Code Last Updated
123 10 15 7 A 12/24/2024
124 5 6 10 B 12/24/2024
123 10 15 7 A 12/26/2024
] (delimiter is '\t')
;
TempTable:
Load 
  Concat('[' & FieldName(RecNo(), 'Data') & ']', ',') as Fields
AutoGenerate NoOfFields('Data')
Where FieldName(RecNo(), 'Data') <> 'Last Updated'
; 
Let vFields = Peek('Fields');
Drop Table TempTable;

Join (Data)
Load Distinct *, Hash128($(vFields)) as Key
Resident Data;

Inner Join (Data)
Load 
  Key,
  Min([Last Updated]) as  [Last Updated]
Resident Data
Group by Key;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com