topic Re: merge columns in QlikView

merge columns

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

Hi All,

I have data like below. I want all the main columns(Main1, Main2, Main3) to be merged into single column in qlikview and data should be in same format.

expected output

Please help!

Re: merge columns

Gysbert_Wassenaar — Thu, 04 Oct 2018 09:55:21 GMT

Perhaps like this:

Data:

LOAD ID,

Main1,

Main2,

Main3,

Replace(

Main1 & chr(10) & Main2 & chr(10) & Main3

, chr(10)&chr(10)

, chr(10)

)

, chr(10)&chr(10)

, chr(10)

) as Concatenated

FROM

c315886.xls

(biff, embedded labels, table is Sheet1$);

Re: merge columns

cmorri1988 — Thu, 04 Oct 2018 10:01:19 GMT

Hi Suraj,

There are many ways to achieve the end result, please note that where I 'CREATE SOURCE DATA', you will use your source file;

//OPTION 1 - CROSSTABLE

/////////////////////////////////////////////////////////////

//CREATE SOURCE DATA
SOURCE:
LOAD * INLINE [
ID, Main1, Main2, Main3
1,AA,DR,ER
1,CC,AQ,YU
1,FF,NP,IK
1,KK
];

//FINAL_WITH_NULLS
FINAL_temp:
crosstable (SOURCE, FINAL, 1)
LOAD *
Resident SOURCE;

//FINAL_WITH_NULLS_REMOVED
FINAL:
        NoConcatenate LOAD *
        Resident FINAL_temp
WHERE NOT IsNull(FINAL);

DROP TABLES SOURCE, FINAL_temp;

DROP FIELD SOURCE FROM FINAL;

//////////////////////////////////////////////////////////////

//OPTION 2 - THREE RESIDENT LOADS

/////////////////////////////////////////////////////////////

//CREATE SOURCE DATA
SOURCE:
LOAD * INLINE [
ID, Main1, Main2, Main3
1,AA,DR,ER
1,CC,AQ,YU
1,FF,NP,IK
1,KK
];

//FINAL_WITH_NULLS_REMOVED
FINAL:
NoConcatenate LOAD
    ID,
    Main1 as FINAL
Resident SOURCE
WHERE LEN(Main1)>0;


Concatenate(FINAL)
LOAD
    ID,
    Main2 as FINAL
Resident SOURCE
WHERE LEN(Main2)>0;

Concatenate(FINAL)
LOAD
    ID,
    Main3 as FINAL
Resident SOURCE
WHERE LEN(Main3)>0;

DROP TABLE SOURCE;

//////////////////////////////////////////////////////////////

//Hope this helps!

Re: merge columns

shwethaa — Thu, 04 Oct 2018 10:20:43 GMT

It can be achieved through cross table load.

Refer this-->> The Crosstable Load

Re: merge columns

surajap123 — Thu, 04 Oct 2018 15:11:58 GMT

Thank you so much Gysbert.