topic Re: Crosstable load from resident table in QlikView

Crosstable load from resident table

BlondeThursday — Sat, 16 Nov 2024 02:05:51 GMT

I have data (from Excel) that looks a bit like:

MonthYear	...	VenABud	VenBBud	VenCBud	VenDBud	VenEBud	...
May-19	...	495	259	430	250	150	...
Jun-19	...	495	280	400	200	125	...
Jul-19	...	495	295	350	280	200

etc.

This has been loaded as part of about 75 other columns like this:

Load
MonthYear,
Venue,
...,
VenABud,
VenBBud,
VenCBud,
VenDBud,
VenEBud,
...

Note that the venue name is actually loaded elsewhere in the script so what I want is the have just the budget for each MonthYear and Venue loaded as a single field.

I've been looking at doing a crosstable load resident from the main table but can't see quite how to use the syntax to create just a budget field for each MonthYear and Venue:

MonthYear	Venue	Budget
May-19	A	495
May-19	B	259
May-19	C	430
May-19	D	250
May-19	E	150
Jun-19	A	495
Jul-19	B	280
Jul-19	C	400

etc.

Any ideas about how I can achieve this?

Thank you for all previous helps.

Re: Crosstable load from resident table

Stoyan_Terziev — Tue, 29 Oct 2019 10:46:31 GMT

Hello,

Below is example which will work regardless of the number of columns you enter, you just need to list them in the first Load statement (keep in mind the MonthYear should come first).

Here is the template script you can use:

Data_temp:
CrossTable(Venue, Budget ,1)
LOAD * INLINE [
MonthYear, VenABud, VenBBud, VenCBud, VenDBud, VenEBud
May-19, 495, 259, 430, 250, 150
Jun-19, 495, 280, 400, 200, 125
Jul-19, 495, 295, 350, 280, 100
];

Data:
NoConcatenate
LOAD
MonthYear,
Mid(Venue,4,1) as Venue,
Budget
RESIDENT Data_temp
;

DROP TABLE Data_temp;

RESULTS:

I hope that helps!

Kind regards,

S.T

Re: Crosstable load from resident table

mohamed_ahid — Tue, 29 Oct 2019 10:55:42 GMT

hello ,

solved here :

https://community.qlik.com/t5/New-to-QlikView/Crosstable-using-a-load-resident/m-p/1107296

Regards

Re: Crosstable load from resident table

BlondeThursday — Tue, 29 Oct 2019 13:32:06 GMT

Thank you S.T.

That worked perfectly. I just had trouble interpreting some of the other posts on the subject to fit my scenario.

All sorted now.

Regards,
Christine