Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Dynamic columns / Crosstable question

HI,

I attach a simple sample application that contains the following data structure:datastructure.jpg

- For every "Movements" record I have one "Items" record as you can see

- The "Movements" record contains the quantity field "Qty" and the "Items" record contains 20 different "price" fields (10 including tax and 10 excluding) e.g. "Price1Excl", "Price1Incl", "Price2Excl" etc.

- Additionally, I have a separate table "PriceHeadings" which contains the descriptions of each of these prices - for example "PriceDesc1" contains the text "RETAIL", "PriceDesc2" the text "WHOLESALE" etc.

The requirement is to produce a table such as the one below where the last column "Value" will be the value based on the "Price" selected from a drop down list that will contain the prices with their descriptive names - e.g. RETAIL, WHOLESALE etc ..

In the example below I am showing Value (which is "Movement" x Price) using the "Price1Excl" price as an illustration.

Hope the above makes sense - I attach the sample application with some data...

I would appreciate your assistance on this as it's been troubling me a lot

Alexis


StockStatementChart.jpgALE etc

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

It makes things easier if you upload a sample that we can reload, e.g. by posting also the source data files or using INLINE tables.

I used a binary load to be able to reload and modify the data model.

See attached for a solution transforming your tables using CROSSTABLE LOAD prefix.

View solution in original post

4 Replies
Highlighted
Not applicable

I would rather normalize the item table and the priceheading table in the loading script first. so they can join each other properly.

Highlighted
Partner
Partner

Thanks cooker li

Can you elaborate please?

Alexis

Highlighted
MVP
MVP

It makes things easier if you upload a sample that we can reload, e.g. by posting also the source data files or using INLINE tables.

I used a binary load to be able to reload and modify the data model.

See attached for a solution transforming your tables using CROSSTABLE LOAD prefix.

View solution in original post

Highlighted
Partner
Partner

Apologies for not loading the QVD files as well.

Your solution is exactly what I was after - thank you so much. Brilliant

Thanks swuehl