Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
kfahri342
New 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

Re: How to merge several columns into one?

The Crosstable Load

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

Re: How to merge several columns into one?

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

Re: How to merge several columns into one?

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

Re: How to merge several columns into one?

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;

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

Re: How to merge several columns into one?

Thanks Anil.

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

Cheers

kfahri342
New Contributor III

Re: How to merge several columns into one?

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

Re: How to merge several columns into one?

There is field called monthname. You can play with that

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

Re: How to merge several columns into one?

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

Re: How to merge several columns into one?

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