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

How to merge several columns into one?

Hi All,

In my underlying data, I have a table like the following:

IDPhase 1Phase 2Phase 3Phase 4Phase 5
System ASept-17Oct-17Nov-17Dec-17Mar-18
System BNov-17Dec-17Jan-18Feb-18Mar-18
System CAug-17Sept-17Oct-17Nov-17Dec-17

And for a particular view, I need to transpose the data into the below structure on Qlikview;

Unfortunately, I am unable to change the structure of the underlying data, so I am hoping I can manipulate the expression & dimensions to create the desired view.

Any ideas are welcome?

Many Thanks

Kozan

Type of PhaseAug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18
Phase 1111
Phase 2111
Phase 3111
Phase 4111
Phase 512

I fgdfgdfgdf

12 Replies
Anil_Babu_Samineni

The Crosstable Load

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kfahri342
Contributor III
Contributor III
Author

I have tried to follow the instructions but with no luck. I can transpose the Phases into one column but the numbers I am receiving don't make any sense. Is anyone able to clarify the steps in more detail please?

Many Thanks

Kozan

effinty2112
Master
Master

Hi Kozan,

Try:

Data:

LOAD * INLINE [

    ID, Phase 1, Phase 2, Phase 3, Phase 4, Phase 5

    System A, Sept-17, Oct-17, Nov-17, Dec-17, Mar-18

    System B, Nov-17, Dec-17, Jan-18, Feb-18, Mar-18

    System C, Aug-17, Sept-17, Oct-17, Nov-17, Dec-17

];

CrossTable:

CrossTable('Type of Phase', 'Date') LOAD * Resident Data;

Drop Table Data;

Then this pivot table:

Type of Phase Date Sept-17Oct-17Nov-17Dec-17Mar-18Jan-18Feb-18Aug-17
Phase 1 1-1----1
Phase 2 11-1----
Phase 3 -11--1--
Phase 4 --11--1-
Phase 5 ---12---

Expression is Count(ID).

Regards

Andrew

Anil_Babu_Samineni

Try this?

Sample:

CrossTable([Type Of Phase], MonthName)

LOAD ID,

     [Phase 1],

     [Phase 2],

     [Phase 3],

     [Phase 4],

     [Phase 5]

FROM

(ooxml, embedded labels, table is Sheet1);

Generic LOAD Distinct [Type Of Phase], MonthName(MonthName) as Name_Month,Count(MonthName) as Value Resident Sample Group By [Type Of Phase], MonthName;

DROP Table Sample;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kfahri342
Contributor III
Contributor III
Author

Thanks Anil.

So the Totals now add up correctly, but how do I get the months into Columns?

Cheers

kfahri342
Contributor III
Contributor III
Author

This is better. I have managed to get the Dates into Columns. But for whatever reason the Dates aren't reconciling., back to the underlying data.

Any ideas why?

Thanks

Anil_Babu_Samineni

There is field called monthname. You can play with that

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
effinty2112
Master
Master

Hi Kozan,

In your inline table your date format is not regular, try this:

Data:

LOAD * INLINE [

    ID, Phase 1, Phase 2, Phase 3, Phase 4, Phase 5

    System A, SEP 17, OCT 17, NOV 17, DEC 17, MAR 18

       System B, NOV 17, DEC 17, JAN 18, FEB 18, MAR 18

       System C, AUG 17, SEP 17, OCT 17, NOV 17, DEC 17

];

TempCrossTable:

CrossTable('Type of Phase', 'Date') LOAD * Resident Data;

NoConcatenate

CrossTable:

LOAD

ID,

[Type of Phase],

Date#(Date,'MMM YY') as Date

Resident TempCrossTable;

drop Table TempCrossTable;

Now this pivot table and now the date can be properly sorted:

Type of Phase Date AUG 17SEP 17OCT 17NOV 17DEC 17JAN 18FEB 18MAR 18
Phase 1 11 1  
Phase 2 11 1  
Phase 3 11 1  
Phase 4 11 1 
Phase 5 1 2

Regards

Andrew

its_anandrjs

This way also make correction on the Month fields Sep is Sept in your data but correct one is Sep

Tmp1:

CrossTable(Phase, Data)

LOAD ID,

    [Phase 1],

    [Phase 2],

    [Phase 3],

    [Phase 4],

    [Phase 5]

FROM

[https://community.qlik.com/message/1365708?et=watches.email.thread#1365708]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

New1:

Load *,Date(Date#(Data,'MMM-DD'),'MMM-DD') as MonthNames

Resident Tmp1

Where Len(Data) > 0;

DROP Table Tmp1;


OP2.PNG