Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: I cant load values with concatination function

Hi,

Load the data as single sheet.

Ex:

Table1:

code1       code2

Not applicable

Re: I cant load values with concatination function

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

Re: I cant load values with concatination function

14684 values

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

mark6505
Not applicable

Re: I cant load values with concatination function

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
Not applicable

Re: I cant load values with concatination function

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
Not applicable

Re: I cant load values with concatination function

Try to use like

LOAD Distinct code1&code2

FROM

[ALl liste.xlsx]

(ooxml, embedded labels, table is Liste);

Not applicable

Re: I cant load values with concatination function

14684 values  with your scripts

Not applicable

Re: I cant load values with concatination function

14690 values brother

pradeepreddy
Not applicable

Re: I cant load values with concatination function

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.