topic Re: Merging 2 columns into 1 column and grouping in QlikView

Merging 2 columns into 1 column and grouping

narender123 — Mon, 19 Jun 2017 06:34:57 GMT

Hi All,

I am creating a table and I have a requirement shown in Below image:

I want to merge Column1 and Column 2 and group by Column 1 as shown result in right side in above image.

Please tell me to achieve the result.

Thanks in advance.

Narender

Re: Merging 2 columns into 1 column and grouping

Anil_Babu_Samineni — Mon, 19 Jun 2017 06:48:01 GMT

Yes, Here you go

TeamData:

LOAD * inline [

SalesGroup|Team|Date|Amount

East|Gamma|01/05/2013|20000

East|Gamma|02/05/2013|20000

West|Zeta|01/06/2013|19000

East|Alpha|01/07/2013|25000

East|Delta|01/08/2013|14000

West|Epsilon|01/09/2013|17000

West|Eta|01/10/2013|14000

East|Beta|01/11/2013|20000

West|Theta|01/12/2013|23000

] (delimiter is '|');

Concat1:

LOAD SalesGroup,Concat(Team,',') as TeamConcat1 Resident TeamData Group By SalesGroup;

LOAD SalesGroup & '(' & TeamConcat1 & ')' as Final Resident Concat1;

Re: Merging 2 columns into 1 column and grouping

kamal_sanguri — Mon, 19 Jun 2017 07:11:21 GMT

Try this:

Re: Merging 2 columns into 1 column and grouping

kamal_sanguri — Mon, 19 Jun 2017 07:16:41 GMT

more simpler than previous sample:

Re: Merging 2 columns into 1 column and grouping

kamal_sanguri — Mon, 19 Jun 2017 07:19:27 GMT

Exactly same as described by you.

Re: Merging 2 columns into 1 column and grouping

antoniotiman — Mon, 19 Jun 2017 07:32:32 GMT

Hi,

try this

Dimension :

Dim1&'('&Aggr(Concat(Dim2,';'),Dim1)&')'

Expression :

Sum(Value)

Regards,

Antonio

Re: Merging 2 columns into 1 column and grouping

narender123 — Mon, 19 Jun 2017 09:44:21 GMT

hI aNIL,

I tried as you suggested but I am getting the repetition of 2nd dimension with 1st dimension.

Column2 showing like A11,A11,A11,A11,A11,A11---------------- against Agriculture.

What i did is like:

Table1:

Load the whole table 1st.

Table2:

Column 1, concat(column2,',') as COLUMN2,

sum(amount) as AMOUNT,

resident Table1

group by Column1;

Please suggest, What I did wrong?

Thanks,

Re: Merging 2 columns into 1 column and grouping

Anil_Babu_Samineni — Mon, 19 Jun 2017 09:52:22 GMT

May be use DISTINCT Keyword

concat(DISTINCT column2,',')

Re: Merging 2 columns into 1 column and grouping

Kushal_Chawda — Mon, 19 Jun 2017 10:04:59 GMT

Create straight table:

Dimension:

Column1:

Expression:

1) Column1-Column2

Column1 & '(' & concat( distinct Column2,',') &')'

2) Amount

Sum(Amount)

Re: Merging 2 columns into 1 column and grouping

narender123 — Tue, 20 Jun 2017 06:53:47 GMT

Hello Anil,

I am facing some problem during setting the concatenation in existing script and not getting the exact result. So I go with the antoniotiman suggestion, as per his suggestion we don't need any change in the script.

But thanks for your suggestion.

Thanks,

Narender

Re: Merging 2 columns into 1 column and grouping

narender123 — Tue, 20 Jun 2017 06:55:35 GMT

Thanks, Antonio Mancini, It's working for me.

I just added Distinct function in it.

Narender

Re: Merging 2 columns into 1 column and grouping

Anil_Babu_Samineni — Tue, 20 Jun 2017 06:56:29 GMT

Good, I thought it was in script level. Anyhow you got the solution. Glad we were able to help you

Re: Merging 2 columns into 1 column and grouping

narender123 — Tue, 20 Jun 2017 06:56:35 GMT

Thanks to All.

Narender