merge columns

[surajap123]

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!

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this:

Data:

LOAD ID,

     Main1,

     Main2,

     Main3,

     Replace(

       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$);

[cmorri1988]

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!

[shwethaa]

It can be achieved through cross table load.

Refer this-->> The Crosstable Load

[surajap123]

Thank you so much Gysbert.