Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Hi Siva,
suggest you share a small sample data set, and the expected output.
Marcus
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.
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;
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.
Siva,
based on the data structure you provided, this will work regardless of the number of customers in your data source.
Marcus
Okay Marcus. I just provided sample data for only 3 customers. But it can be more than 100.
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
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.