topic I cant load values with concatination function in QlikView

I cant load values with concatination function

Mon, 21 Dec 2015 08:09:11 GMT

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

Re: I cant load values with concatination function

ajsjoshua — Mon, 21 Dec 2015 08:32:32 GMT

Hi,

Load the data as single sheet.

Ex:

Table1:

code1 code2

Re: I cant load values with concatination function

Mon, 21 Dec 2015 08:41:09 GMT

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

Re: I cant load values with concatination function

Mon, 21 Dec 2015 08:44:58 GMT

14684 values

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

Re: I cant load values with concatination function

Mark_Little — Mon, 21 Dec 2015 08:55:45 GMT

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

Re: I cant load values with concatination function

Peter_Cammaert — Mon, 21 Dec 2015 09:02:04 GMT

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

Re: I cant load values with concatination function

senpradip007 — Mon, 21 Dec 2015 09:05:42 GMT

Try to use like

LOAD Distinct code1&code2

FROM

[ALl liste.xlsx]

(ooxml, embedded labels, table is Liste);

Re: I cant load values with concatination function

Mon, 21 Dec 2015 09:12:43 GMT

14684 values with your scripts

Re: I cant load values with concatination function

Mon, 21 Dec 2015 09:26:42 GMT

14690 values brother

Re: I cant load values with concatination function

PradeepReddy — Mon, 21 Dec 2015 09:35:31 GMT

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.

Re: I cant load values with concatination function

tamilarasu — Mon, 21 Dec 2015 09:38:07 GMT

Hi,

You have some blank cells in column "code B" . Blank cells are filled with single space.

Count of trimmed cells (unique) = 14688

Count of untrimmed cells (unique) = 14699

QlikView removes the extra spaces. So the count is correct. If you still want to match with the excel pivot table, you can follow Peter's solution.

SET Verbatim = 1;
LOAD Distinct Text(code1&code2) as Concat
FROM
[ALl liste.xlsx]
(ooxml, embedded labels, table is Liste);

Check the attachments.

Re: I cant load values with concatination function

Mon, 21 Dec 2015 09:42:48 GMT

Hello

i am juste beginner in the Qlikview script , how i make this variable oF SET statement

give me the script please because i put SET Verbatim = 1; before load , the result in listBOx it found 14690 but not 14699

Directory;

SET Verbatim = 1;

LOAD Distinct code1&code2 as concat

FROM .....

Thanks

Re: I cant load values with concatination function

tamilarasu — Mon, 21 Dec 2015 09:50:19 GMT


SET Verbatim = 1;  
LOAD Distinct Text(code1&code2) as Concat  
FROM  
[ALl liste.xlsx]  
(ooxml, embedded labels, table is Liste);

Did you check the attachment. It shows count as 14699.

Re: I cant load values with concatination function

PradeepReddy — Mon, 21 Dec 2015 09:50:31 GMT

SET Verbatim = 1;

Directory;

LOAD text(code1)&'-'&text(code2)

FROM

[ALl liste.xlsx]

(ooxml, embedded labels, table is Liste);

Re: I cant load values with concatination function

Mon, 21 Dec 2015 09:55:17 GMT

Thanks all , finally i find 14699 value with response of pradeept

so when we want to load values without probléme of spaces or values started by 0 or values terminate by , we use this variable SET SET Verbatim = 1; ???

Thanks an other one

Re: I cant load values with concatination function

Peter_Cammaert — Mon, 21 Dec 2015 10:22:29 GMT

Besides the trimming, there may be another QlikView behavior at play: a numerical value can have only one string representation in the same field. QlikView is a data analysis solution. Everything that looks like a number will be treated as a number, meaning that 00001 and 1 are value-wise exactly the same to QlikView if you don't block the software from doing what it is designed to do. The fast interpretation that is now causing trouble has a very big advantage: speed.

I don't really knoiw why you are concatenating these two columns, but I guess that there is some sort of ID or key-value creation involved. You can always force QlikView to interprete your expression results as literal values by prefixing them with a character like 'K' for Key or 'ID' for Identification. For example:

LOAD 'K' & code1 & code2

FROM Excel (options);

will force QlikView to treat everything after the first concatenation operator as text. Therefor you can omit the text() function calls which makes your script a bit simpler.

However the Verbatim-trick is still needed to block QLikView from stripping the spaces.

Best,

Peter

Re: I cant load values with concatination function

Sun, 27 Dec 2015 14:15:19 GMT

hi ,

if i have sql request to loading from DB sqlserver how do i do for getting this number

connect ....

SQl select ....

from..

thanks all,