topic Re: Similar to crossover table in App Development

Similar to crossover table

qvdrago — Fri, 22 Sep 2023 14:47:14 GMT

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.

Re: Similar to crossover table

anat — Sat, 23 Sep 2023 12:41:08 GMT

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

Re: Similar to crossover table

rwunderlich — Sun, 24 Sep 2023 10:08:26 GMT

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

Re: Similar to crossover table

qvdrago — Mon, 25 Sep 2023 08:22:13 GMT

Many Thank Rob.

Work perfectly

You are always the best !!!

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

Many thansk

Re: Similar to crossover table

rwunderlich — Tue, 26 Sep 2023 10:06:35 GMT

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

-Rob

Re: Similar to crossover table

qvdrago — Tue, 26 Sep 2023 10:59:17 GMT

Data:

LOAD * Inline [

Season

W20

W21

W22

W23

S20

S21

S22

S23

S24]

Transform to

Re: Similar to crossover table

qvdrago — Tue, 26 Sep 2023 11:01:15 GMT

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

Re: Similar to crossover table

rwunderlich — Sat, 30 Sep 2023 09:33:20 GMT

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/Generic.htm

-Rob