Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Compare values in the same table

Guys,

I have the table as shown below,

Screenshot_17.png

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.

7 Replies
prieper
Master II
Master II

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

vishsaggi
Champion III
Champion III

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

Anonymous
Not applicable
Author

Please find the attached the sample data.

Anonymous
Not applicable
Author

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...

Anonymous
Not applicable
Author

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

effinty2112
Master
Master

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

Anonymous
Not applicable
Author

Guys,

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