topic Re: Change column content in query in QlikView

Change column content in query

Fri, 01 Aug 2014 12:35:03 GMT

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!

Re: Change column content in query

joshabbott — Fri, 01 Aug 2014 12:40:51 GMT

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!

Re: Change column content in query

joshabbott — Fri, 01 Aug 2014 12:43:20 GMT

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?

Re: Change column content in query

Fri, 01 Aug 2014 12:45:19 GMT

Yes they do!

Re: Change column content in query

Fri, 01 Aug 2014 12:48:30 GMT

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.

Re: Change column content in query

joshabbott — Fri, 01 Aug 2014 13:03:07 GMT

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

Re: Change column content in query

Mon, 04 Aug 2014 12:53:08 GMT

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?

Re: Change column content in query

ashfaq_haseeb — Mon, 04 Aug 2014 12:56:23 GMT

use noconcatenate in between two tables.

Regards

ASHFAQ

Re: Change column content in query

joshabbott — Mon, 04 Aug 2014 12:57:10 GMT

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

Re: Change column content in query

Mon, 04 Aug 2014 13:52:55 GMT

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

Re: Change column content in query

joshabbott — Mon, 04 Aug 2014 13:57:30 GMT

Hmmm, that sounds like a different problem. That's no good

Re: Change column content in query

Mon, 04 Aug 2014 16:03:47 GMT

After playing around a little bit more, it seems that the problem is with the statement

left keep (RA_raw) load ....

MatDiv1 as MatDiv

where Division = 1....

left keep (RA_raw) load ....

MatDiv2 as MatDiv

where Division <> 1....

and if I drop the left keep (RA_raw), then it doesn´t crash anymore. Now I just need to do the left keep functionality at another point in time in the script....

Re: Change column content in query

Wed, 20 Aug 2014 08:34:54 GMT

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