Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
surajap123
Creator II
Creator II

merge columns

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.

data image.bmp

expected output

output.JPG

Please help!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
cmorri1988
Creator
Creator

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
Contributor III
Contributor III

It can be achieved through cross table load.

Refer this-->> The Crosstable Load

surajap123
Creator II
Creator II
Author

Thank you so much Gysbert.