Similar to crossover table

[qvdrago]

Hi, 

I have a table with several records, every record has a field 'Season' that can contain values like: 'W20', 'W21', 'W22', 'W23', 'S20', 'S22', 'S23', 'S24'...

where 'W' stays for 'Winter' and 'S' stays for 'Summer'.

I'd like to create a summary table like this:

Group, Season1, Season2, Season3, Season4

Summer, S24, S23, S22, S20

Winter, W23, W22, W21, W20

Thank you.

[anat]

can you post  your actual source data format and sample records ?

[rwunderlich · Accepted solution]

How about:

Data:

LOAD * Inline [

Season

W20

W21

W22

W23

S20

S21

S22

S23

S24

];

SeasonMap:

Mapping Load * Inline [

Code, Season

W, Winter

S, Summer

];

Groups:

Generic Load Group, SeasonSeq, Season

;

Summary:

LOAD *,

'Season' & AutoNumber(Season, Group) as SeasonSeq

;

LOAD

Season,

ApplyMap('SeasonMap',left(Season, 1)) as Group

Resident Data

Order By Season DESC

;

If you need to join the Groups tables into a single table see: https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[qvdrago · Accepted solution]

Many Thank Rob. 

Work perfectly 

You are always the best !!!

Just for information .... Which function should I use to bring data horizontally ???

Many thansk

 

[rwunderlich]

Can you show an example of whaat you mean by "bring data horizontally"?

-Rob

[qvdrago]

Data:

LOAD * Inline [

Season

W20

W21

W22

W23

S20

S21

S22

S23

S24]

Transform to

[qvdrago]

Hi Rob

Thank you for your time

Data:

LOAD * Inline [

Season

W20

W21

W22

W23

S20

S21

S22

S23

S24]

Transform all data in 2 lines 

Regards

Clod

[rwunderlich]

It is the "Generic" prefix that transforms from rows (vertical) to columns (horizontal).

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes...

-Rob