topic Crosstable help (single row into multiples) in QlikView

Crosstable help (single row into multiples)

DMG — Wed, 25 Nov 2020 16:16:04 GMT

Hi, i've tried searching the community and there is lots of different examples of things similar to what i'm trying to achieve, but none quite solve my issues, i'm not sure if crosstable will work and if i have the syntax correct.

I have a table loaded into qlik from excel, with a format roughly like

Person, Type, Team, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12

For example

John Doe, Normal, Ops, 1,1,1,1,1,1,1,1,1,1,1,1

Sue Doe, Temp, Ops, 0,0,0,1,1,1,1,1,0,0,0,0

I would like to create a new table, that creates a separate row of date for each person and each period, for example

Person, Type, Team, Period, Value
John Doe, Normal, Ops, 1, 1

John Doe, Normal, Ops, 2, 1

John Doe, Normal, Ops, 3, 1

Is that something i should achieve via crossjoin or another method during an extra script section of the data load ?

Re: Crosstable help (single row into multiples)

Taoufiq_Zarra — Mon, 24 Aug 2020 10:35:06 GMT

Hi,

Maye be this :

Data: CrossTable(Period, Value, 3) load * inline [ Person, Type, Team, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12 John Doe, Normal, Ops, 1,1,1,1,1,1,1,1,1,1,1,1 Sue Doe, Temp, Ops, 0,0,0,1,1,1,1,1,0,0,0,0 ]; Final: noconcatenate load Person, Type, Team, keepchar(Period,'0123456789') as Period,Value resident Data; drop table Data;

output :

Re: Crosstable help (single row into multiples)

DMG — Mon, 24 Aug 2020 11:24:53 GMT

Thanks, the output looks perfect, i'm very new to qlik however, if i wanted to take the data from an existing already loaded table (that contains many fields in addition to those needed for the crosstable) how would i alter the load inline section, to instead take certain fields for example , Full_Name (instead of Person), Type, Team, P1,...P12 from an existing table called "Resource" rather than loading the data inline.

Thanks

Re: Crosstable help (single row into multiples)

Taoufiq_Zarra — Mon, 24 Aug 2020 11:49:25 GMT

if I understood correctly, you need

Data: CrossTable(Period, Value, 3) load Full_Name as Person, Type, Team, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12 resident Resource; Final: noconcatenate load Person, Type, Team, keepchar(Period,'0123456789') as Period,Value resident Data; drop table Data;

Re: Crosstable help (single row into multiples)

Brett_Bleess — Tue, 08 Sep 2020 15:46:49 GMT

Just adding a Design Blog post as well:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

Regards,
Brett