Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

converting fields as subfields

Hi I Have table like below...

    

Soccer Teams Leagueperfomence20082009201020112012
dallasMajorwon1520172219
dallasMajorlost75734
sundownsPSLwon112217923
sundownsPSLlost1079124
man cityEnglishwon2528312220
man cityEnglishlost853711
G RangersScottishwon1517131911
G RangersScottishlost35216
Real Madridspainwon3027322433
Real Madridspainlost354102

I want to format as below.How can I do that in qlik sense ?

   

Soccer TeamLeagueResultsSeasonPerfomance
dallasMajorlost20087
dallasMajorlost20095
dallasMajorlost20107
dallasMajorlost20113
dallasMajorlost20124
dallasMajorwon200815
dallasMajorwon200920
dallasMajorwon201017
dallasMajorwon201122
dallasMajorwon201219
G RangersScottishlost20083
G RangersScottishlost20095
G RangersScottishlost20102
G RangersScottishlost20111
G RangersScottishlost20126
G RangersScottishwon200815
G RangersScottishwon200917
G RangersScottishwon201013
G RangersScottishwon201119
G RangersScottishwon201211
man cityEnglishlost20088
man cityEnglishlost20095
man cityEnglishlost20103
man cityEnglishlost20117
man cityEnglishlost201211
man cityEnglishwon200825
man cityEnglishwon200928

Thanks you

4 Replies
oknotsen
Honored Contributor III

Re: converting fields as subfields

Use the CROSSTABLE function before your load script.

Crosstable ‒ Qlik Sense

In this case, your script should look something like this:

CROSSTABLE(Season, Performance, 3)

LOAD

[Soccer Team],

League,

Result,

[2008],

[2009],

...

FROM someSource

;

May you live in interesting times!
sasiparupudi1
Honored Contributor III

Re: converting fields as subfields

temp1:

LOAD

*

Inline

[

Soccer Teams,League,perfomence,2008,2009,2010,2011,2012

dallas,Major,won,15,20,17,22,19

dallas,Major,lost,7,5,7,3,4

sundowns,PSL,won,11,22,17,9,23

sundowns,PSL,lost,10,7,9,12,4

man city,English,won,25,28,31,22,20

man city,English,lost,8,5,3,7,11

G Rangers,Scottish,won,15,17,13,19,11

G Rangers,Scottish,lost,3,5,2,1,6

Real Madrid,spain,won,30,27,32,24,33

Real Madrid,spain,lost,3,5,4,10,2

] ;

RENAME Field perfomence to Result;

CrossTable(Season,perfomence,3)

tabcross:

Load * Resident temp1;

oknotsen
Honored Contributor III

Re: converting fields as subfields

Why would you do it in two loads instead of just one?

May you live in interesting times!
sasiparupudi1
Honored Contributor III

Re: converting fields as subfields

I thought you wanted to rename the fields thats why

Community Browser