Dynamic columns / Crosstable question

[alexis]

HI,

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

- 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

ALE etc

[#2]

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

[alexis]

Thanks cooker li

Can you elaborate please?

Alexis

[swuehl · Accepted solution]

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]

Apologies for not loading the QVD files as well.

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

Thanks swuehl