topic Re: Compare values in the same table in QlikView

Compare values in the same table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Guys,

I have the table as shown below,

Here I want to create a unique key for the customers for the columns (FieldName, Data type, Min Occurs, Max Occurs, M/C/O and Length) using Autonumber function in the script as shown below,

Autonumber(FieldName&[Data type]&[ Min Occurs]&[Max Occurs]&[M/C/O]&Length) as UniqueKey

Based on these keys, I need to compare and the store the matched and not matched values in a table based on the unique column Customer.

Please help me out to achieve this by script wise.

Thanks,

Bose.

Re: Compare values in the same table

prieper — Tue, 25 Jul 2017 13:45:59 GMT

aircode:

OldData:
LOAD
Autonumber(FieldName&[Data type]&[ Min Occurs]&[Max Occurs]&[M/C/O]&Length) as UniqueKey,
...
FROM ....;
NewData:
NOCONCATENATE LOAD
IF(EXISTS(UniqueKey, Autonumber(FieldName&[Data type]&[ Min Occurs]&[Max Occurs]&[M/C/O]&Length),
'Known', 'New')          AS EntryCheck,
....
FROM ....;

Thereafter you may filter the new list for the field "EntryCheck" and STORE into a QVD or as csv-file.

HTH

Peter

Re: Compare values in the same table

vishsaggi — Tue, 25 Jul 2017 13:49:33 GMT

What is your expected output? Can you share some sample data rather than image?

Re: Compare values in the same table

Anonymous — Tue, 25 Jul 2017 13:55:04 GMT

Please find the attached the sample data.

Re: Compare values in the same table

Anonymous — Tue, 25 Jul 2017 14:16:06 GMT

My Expected Output:

1. if customer, Addivant gets selected, No fields of Addivant exactly matched with Cargosmart in that combination

but 4 fields of Addivant exactly matched with Hapag Lloyd.

This is the output I'm expecting...

Re: Compare values in the same table

Anonymous — Tue, 25 Jul 2017 14:35:36 GMT

Thanks Peter. But I need this to split up the matched count based on the customers.

Re: Compare values in the same table

effinty2112 — Tue, 25 Jul 2017 14:44:14 GMT

Hi Siva,

Can I make a couple of suggestions?

Instead of

Autonumber(FieldName&[Data type]&[ Min Occurs]&[Max Occurs]&[M/C/O]&Length) as UniqueKey

maybe

Autonumber(FieldName & '|' & [Data type] & '|' & [ Min Occurs] & '|' & [Max Occurs] & '|' & [M/C/O]&Length,'UniqueKey') as UniqueKey

The '|' character is useful here.

'AB' & C' = 'ABC' = 'A' & 'BC'

but

'AB' & '|' & 'C' = 'AB|C'

'A' & '|' & 'BC' = 'A|BC'

also adding the text parameter 'UniqueKey' means you can use a different autonumber function elsewhere in your script and if you give the second one a different text parameter they won't be mixed up together during script execution.

Cheers

Andrew

Re: Compare values in the same table

Anonymous — Tue, 25 Jul 2017 14:52:43 GMT

Guys,

I want this comparison of combination to be performed based on the customers. But this is not as expected. Please help.