Change column content in query

[#1]

Dear community,

this is my first post as total rookie to QlikView. I got two tables like

Sales

Year

Month

Customer

Division

MatDiv1

MatDiv2

Sales amount

and

ProductHierarchy

Material

Product Family

MatDiv1 is populated only for division 1, MatDiv2 is populated only for division 2.

I want to have the material in a single column and apply apply the same lookup {(left keep (Sales) load ... from ProductHierarchy} to both Div1-Material and Div2-Material, in the script. How can I best do this?

Many thanks in advance!

[joshabbott]

You can do a number of things.  If you want to combine these two table's into 1 table in the script, look at doing a left join or mapping load.  Here is an example of a mapping load:

Product:

Mapping Load

  Material, ProdFamily

From [SOURCE];

Sales:

Load

  Year

  ,Month

  ,Customer

  ,Division

  ,Div1-Material

  ,Div2-Material

  ,SalesAmount
  ,ApplyMap('Product', Div1-Material) as ProdFamily

From [Source]

Also remember, if you want to keep this as two table,s and have the selections work, associations are made by columns being named the same, so ni Product, you could rename your 'Material' column to 'Div1-Material' in your script.

Good luck!

[joshabbott]

It looks like the post changed a little from when I originally did my reply   Does the values in MatDiv1 and MatDiv2 corespond to Material in your second table?

[#4]

Yes they do!

[#5]

Thanks for your proposal, Josh. I would like to keep the two tables but have the Div1-Material and the Div2-Material in a single column (say MyMaterial), to which I then apply the lookup to the product hierarchy.

When building charts etc, you would only deal with a single column, which is more convenient.

[joshabbott]

Would it be ok to load MatDiv1 and MatDiv2 into the same column called MatDiv or do these need to be separated?  If you could load them into one column, try something like:

Load

  Year

  ,Month
  ,Customer

  ,Division

  ,MatDiv1 as MatDiv

  ,Sales amount

from [SOURCE];

Load

  Year

  ,Month

  ,Customer

  ,Division

  ,MatDiv2 as MatDiv

  ,Sales Amount

from [SOURCE];

(The top two tables will concatenate on eachother and may create a problem if you are unable to do this.

Load

  Material as MatDiv

  [Product Family]

FROM [Source];

[#7]

Thanks Josh. It works if I load with a where statement ( either " where Division <> 1 " or alternatively " where Division = 1 "), however it crashes if I run the two load statements. It seems the concatenation of tables you mentioned is failing.

Any suggestions?

[ashfaq_haseeb]

Hi

use noconcatenate in between two tables.

Regards

ASHFAQ

[joshabbott]

What do you mean by 'it crashes'?  Could you upload your qvw or a portion of it to make it more understandable?

[#10]

I´m working with a personal edition. By crashing I mean simply that the application stands still after I reload.