Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
BlondeThursday
Contributor III
Contributor III

Crosstable load from resident table

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

MonthYear...VenABudVenBBudVenCBudVenDBudVenEBud...
May-19...495259430250150...
Jun-19...495280400200125...
Jul-19...495295350280

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:

MonthYearVenueBudget
May-19A495
May-19B259
May-19C430
May-19D250
May-19E150
Jun-19A495
Jul-19B280
Jul-19C400

etc.

Any ideas about how I can achieve this?

Thank you for all previous helps.

Labels (4)
1 Solution

Accepted Solutions
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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:

image.png

I hope that helps!

 

Kind regards,

S.T

View solution in original post

3 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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:

image.png

I hope that helps!

 

Kind regards,

S.T

mohamed_ahid
Partner - Specialist
Partner - Specialist

BlondeThursday
Contributor III
Contributor III
Author

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