Comparison using loop

[Anonymous]

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.

[tomasz_tru]

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;

[marcus_malinow]

Hi Siva,

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

Marcus

[Anonymous]

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.

[marcus_malinow]

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;

[Anonymous]

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.

[marcus_malinow]

Siva,

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

Marcus

[Anonymous]

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

[antoniotiman]

Hi Siva,

maybe this

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 ; 

Table in Front End

Dimension CustomerID,C1

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

Regards,

Antonio

[Anonymous]

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.