topic Re: Comparison using loop in QlikView

Comparison using loop

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

Guys,

Assume I have 8 customers. I need the fields of each customer to be compared with the fields of other customers. For this, I need to do looping in script right?

Could you guys please share some sample code to achieve this comparison using loop functions(for, for each or while), so I can get an idea on achieving this.

Thanks,

Siva.

Re: Comparison using loop

tomasz_tru — Wed, 02 Aug 2017 13:53:10 GMT

If it's not a big dataset you can outer join the table with itself:

customerTable:
Load * inline [
Customer, value
'A', 1
'B', 2
'C', 3
];

ComparisonTable:
OUTER JOIN (customerTable)
LOAD Customer AS Customer_compare, value AS value_compare RESIDENT customerTable;

Re: Comparison using loop

marcus_malinow — Wed, 02 Aug 2017 13:54:22 GMT

Hi Siva,

suggest you share a small sample data set, and the expected output.

Marcus

Re: Comparison using loop

Anonymous — Wed, 02 Aug 2017 14:05:16 GMT

Attached some sample data and my expected output is,

For CustomerID = 1,

Matching Fields = 6 in CustomerID = 2 and

Matching Fields = 1 in CustomerID = 3

I kindly want to achieve this calculation in script itself by doing any of the looping functions and not by expression.

Thanks,

Siva.

Re: Comparison using loop

marcus_malinow — Wed, 02 Aug 2017 14:17:27 GMT

No need to use a loop. The code below does create a couple of synthetic keys, but these are easily dealt with.

TmpCustomer:
LOAD FieldName,
Customer,
CustomerID
FROM

(ooxml, embedded labels, table is Sheet1);

Customer1:
LOAD FieldName as FieldName1,
Customer as Customer1,
CustomerID as CustomerID1
RESIDENT TmpCustomer;

Customer2:
LOAD FieldName as FieldName2,
Customer as Customer2,
CustomerID as CustomerID2
RESIDENT TmpCustomer;

CustomerLink:
NOCONCATENATE
LOAD FieldName as FieldName1,
FieldName as FieldName2,
CustomerID as CustomerID1
RESIDENT TmpCustomer;

INNER JOIN (CustomerLink)
LOAD FieldName as FieldName1,
FieldName as FieldName2,
CustomerID as CustomerID2
RESIDENT TmpCustomer
;

DROP TABLE TmpCustomer;

Re: Comparison using loop

Anonymous — Wed, 02 Aug 2017 14:46:34 GMT

Thanks Marcus. But I have more than 100 customers in my scenario. In such cases, I need to cover all the possibilities. So only I asked for looping scenario but this method won't help in my case.

Re: Comparison using loop

marcus_malinow — Wed, 02 Aug 2017 15:02:55 GMT

Siva,

based on the data structure you provided, this will work regardless of the number of customers in your data source.

Marcus

Re: Comparison using loop

Anonymous — Wed, 02 Aug 2017 15:09:09 GMT

Okay Marcus. I just provided sample data for only 3 customers. But it can be more than 100.

Re: Comparison using loop

antoniotiman — Wed, 02 Aug 2017 15:09:34 GMT

Hi Siva,

maybe this

Table in Front End

Dimension CustomerID,C1

Expression Count(If(CustomerID <> C1,FieldName))

Regards,

Antonio

Re: Comparison using loop

Anonymous — Wed, 02 Aug 2017 15:18:02 GMT

Thanks Antonio, This is what my expected result. But I don't want to achieve this in front end through expressions. I want to achieve it in script itself by getting the values in dimensions.

Re: Comparison using loop

antoniotiman — Wed, 02 Aug 2017 15:38:47 GMT

Table:
LOAD FieldName,
[Data type],
[Min Occurs],
[Max Occurs],
[M/C/O],
Length,
Customer,
CustomerID
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1318479-290153/sum.xlsx"
(ooxml, embedded labels, table is Sheet1);
Join LOAD FieldName as FieldName ,CustomerID as C1
Resident Table ;
Left Join LOAD CustomerID,C1,Count(If(CustomerID <> C1,1)) as Counter
Resident Table Group By CustomerID,C1;
NoConcatenate
LOAD * Resident Table Where Counter <> 0;
Drop Table Table;

Owever change the original number of Records.

Re: Comparison using loop

Anonymous — Wed, 02 Aug 2017 16:27:00 GMT

Thank you so much Antonio. It's great. But I have another requirement like this. Could you please clear me that one too?