Discussion Board for collaboration on QlikView Scripting.
Hello all members
I have a big probléme when i load data from my exel file.
i have two columns code1 and code2 , i concatinate them in the exel and i use the pivote table i found 14999 values ,but when i concatinated them in qlikview i found 14684 , i had 3 day to resolve this probléme and i cant resolve it
My question , how i can load all values from the concatination code1 and code2 without dublicates values
My file is attached coontaint the liste code and the pivote table .
For the second is the qlikview file
I would expect your approach to have worked fine.
text(code1)&text(code2) as Field
I would alias it, but i would have expected that would be fine.
I would try a count(Field) in a text box, check the number.
The next thing i would check, would be to add a RowNo() like below
RowNo()&text(code1)&text(code2) as Field
see what is returned
Your problem occurs because QlikView has a tendency to trim values, cutting leading and trailing spaces (just spaces) from what looks like a value. It does this for all value types.
If you load the column Étiquettes de lignes from the Excel Pivot in your demo QVW as well, and put the field in a listbox next to the concatenation result, you'll see that in both listboxes there are other values at the top. Culprit: spaces.
You can block this trimming by setting a variable in your script before the LOAD statements:
SET Verbatim = 1;
As suggested by Peter, if you use the bellow SET statement you will get the proper results.
SET Verbatim = 1;
In excel sheet,
Total Records: 34,546
Unique records(Based on Code1 & Code2) : 14,699
Table: (Data Model) - 34,546
List Box Contains unique values only. So normal load gives the result of '14,684'.
If you use the SET Statement, it will give the result of '14,699' records
So choose the proper appraoch as per your requirement.