Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Change column content in query

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!

1 Solution

Accepted Solutions
Not applicable
Author

The solution was ultimately an if statement in the initial load statement, works perfect!

View solution in original post

12 Replies
joshabbott
Creator III
Creator III

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
Creator III
Creator III

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?

Not applicable
Author

Yes they do!

Not applicable
Author

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
Creator III
Creator III

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];

Not applicable
Author

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
Champion III
Champion III

Hi

use noconcatenate in between two tables.

Regards

ASHFAQ

joshabbott
Creator III
Creator III

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

Not applicable
Author

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