Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
Partner - Specialist
Partner - Specialist

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
swuehl
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
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.

alexis
Partner - Specialist
Partner - Specialist
Author

Thanks cooker li

Can you elaborate please?

Alexis

swuehl
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.

alexis
Partner - Specialist
Partner - Specialist
Author

Apologies for not loading the QVD files as well.

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

Thanks swuehl