Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

I cant load values with concatination function

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

16 Replies
ajsjoshua
Specialist
Specialist

Hi,

Load the data as single sheet.

Ex:

Table1:

code1       code2

Not applicable
Author

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

Not applicable
Author

14684 values

2015-12-21 9:37 GMT+01:00 joshua solomon <qcwebmaster@qlikview.com>:

Mark_Little
Luminary
Luminary

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

senpradip007
Specialist III
Specialist III

Try to use like

LOAD Distinct code1&code2

FROM

[ALl liste.xlsx]

(ooxml, embedded labels, table is Liste);

Not applicable
Author

14684 values  with your scripts

Not applicable
Author

14690 values brother

PradeepReddy
Specialist II
Specialist II

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.