I cant load values with concatination function

[#1]

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

[tamilarasu]

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.

[#3]

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

[tamilarasu]

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.

[PradeepReddy]

SET Verbatim = 1;

Directory;

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

FROM

[ALl liste.xlsx]

(ooxml, embedded labels, table is Liste);

[#6]

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

[Peter_Cammaert]

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

[#8]

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,