How to merge several columns into one?

[kfahri342]

Hi All,

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

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

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 Phase	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18
Phase 1	1	1		1
Phase 2		1	1		1
Phase 3			1	1		1
Phase 4				1	1		1
Phase 5					1			2

I fgdfgdfgdf

[Anil_Babu_Samineni]

The Crosstable Load

[kfahri342]

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]

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-17	Oct-17	Nov-17	Dec-17	Mar-18	Jan-18	Feb-18	Aug-17
Phase 1		1	-	1	-	-	-	-	1
Phase 2		1	1	-	1	-	-	-	-
Phase 3		-	1	1	-	-	1	-	-
Phase 4		-	-	1	1	-	-	1	-
Phase 5		-	-	-	1	2	-	-	-

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;

[kfahri342]

Thanks Anil.

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

Cheers

[kfahri342]

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

[effinty2112]

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 17	SEP 17	OCT 17	NOV 17	DEC 17	JAN 18	FEB 18	MAR 18
Phase 1		1	1		1
Phase 2			1	1		1
Phase 3				1	1		1
Phase 4					1	1		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;