topic Re: Concat using dynamic string in QlikView

Concat using dynamic string

raajaswin — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

My Input,

KEY	TEXT1	TEXT2
1	A	B
2	A	B
3		B
4	A
5

Required Output,

KEY	OUTPUT
1	A,B
2	A,B
3	B
4	A
5

Need to do it in script,

As of now am using some nested if and & operator, do we have direct function??, can we do it with concat??

Re: Concat using dynamic string

swuehl — Thu, 08 Dec 2016 12:49:24 GMT

You can transform your input table using CROSSTABLE LOAD prefix into a table with structure KEY, TEXT, and TEXT ID, then aggregate (using Concat() ) this new table TEXT values Grouped by KEY.

The Crosstable Load

Re: Concat using dynamic string

raajaswin — Thu, 08 Dec 2016 12:49:41 GMT

if(isnull(TEXT1),TEXT2,if(isnull(TEXT2),TEXT1,TEXT1&','&TEXT2))

this is what am doing as of now

Re: Concat using dynamic string

raajaswin — Thu, 08 Dec 2016 12:50:40 GMT

i have many more columns and lakhs of rows like this, I dont think cross table can be formed for this

Re: Concat using dynamic string

Aurelien_Martinez — Thu, 08 Dec 2016 13:09:57 GMT

Hi,

If you have more than one field change the number 1

data:
CrossTable(TEXT, toto, 1)
LOAD * INLINE [
KEY, TEXT1, TEXT2
1, A, B
2, A, B
3, , B
4, A
5
];

LOAD
KEY,
Concat(toto, ', ') as tt
Resident data
Where Len(toto)<>0
Group By KEY
;

Re: Concat using dynamic string

sunny_talwar — Thu, 08 Dec 2016 13:17:42 GMT

So what is the issue with this? This isn't working?

Re: Concat using dynamic string

Anil_Babu_Samineni — Thu, 08 Dec 2016 13:24:12 GMT

Does this working same as this from Preceding Load

(Text1 & ',' & Text2) as Output

Re: Concat using dynamic string

raajaswin — Thu, 08 Dec 2016 13:38:16 GMT

Am looking for any direct function.. I will try out this way and let u know.. Thank u

Re: Concat using dynamic string

raajaswin — Thu, 08 Dec 2016 13:39:12 GMT

This will give me an additional comma, when Text 2 is blank

Re: Concat using dynamic string

raajaswin — Thu, 08 Dec 2016 13:39:49 GMT

Ya what i was doing is crct nly,, Just wanted to know is there any direct fuction for this??

Re: Concat using dynamic string

Anil_Babu_Samineni — Thu, 08 Dec 2016 13:44:37 GMT

I am sure, There is no direct function for this

In Excel, We do have direct option like

Concatenate(Cell1 & "," & Cell2)

But qlikview has limited functionality.

Re: Concat using dynamic string

raajaswin — Thu, 08 Dec 2016 13:46:03 GMT

Then I will go with Ifs only.. Thank u

Re: Concat using dynamic string

raajaswin — Wed, 28 Dec 2016 13:09:18 GMT

Hi Every one,

Concat works fine, but I have a small problem kindly refer the attachment

Re: Concat using dynamic string

sunny_talwar — Wed, 28 Dec 2016 13:17:47 GMT

May be try this:

data:

CrossTable(TEXT, toto, 1)

LOAD * INLINE [

KEY, A, B,C

1, A, B,1

2, A, B

3, , B ,1

4, A, ,1

];

LOAD

KEY,

Concat(toto, ',', AutoNumber(TEXT)) as tt

Resident data

where len(toto)<>0

Group By KEY

;

Re: Concat using dynamic string

raajaswin — Wed, 28 Dec 2016 13:19:21 GMT

Yo thats it. Thank you sunny