Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks all
Hi,
Load the data as single sheet.
Ex:
Table1:
code1 code2
thanks for your response
yes that is what i do , i work with only one sheet that containt Code1 code2 but i cant load the number that i want
14684 values
2015-12-21 9:37 GMT+01:00 joshua solomon <qcwebmaster@qlikview.com>:
Hi,
I would expect your approach to have worked fine.
Load
text(code1)&text(code2) as Field
From ...
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
Load
RowNo()&text(code1)&text(code2) as Field
From ...
see what is returned
Mark
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;
Best,
Peter
Try to use like
LOAD Distinct code1&code2
FROM
[ALl liste.xlsx]
(ooxml, embedded labels, table is Liste);
14684 values with your scripts
14690 values brother
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
In QlikView,
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.