topic Modifying the value of a Joined Column in QlikView

Modifying the value of a Joined Column

Mon, 21 Nov 2011 17:51:38 GMT

Hey folks,

I've got a scripting question that I'm hoping for some help with. I've created a sample script and attached a sample qlikview document.

Here is my test script:

Orders:

LOAD * Inline

[

OrderNumber, OrderLine, ItemNumber

10001, 1, PART-A

10002, 1, PART-B

10002, 2, PART-C

];

MaterialDemands:

LOAD * Inline

[

OrderNumber, OrderLine, WorkOrder

10001, 1, 20000

10002, 1, 20001

];

Join (Orders)

LOAD

OrderNumber,

OrderLine,

'Y' AS ProjectBased

Resident MaterialDemands;

Which results in the following:

OrderLine	OrderNumber	ItemNumber	ProjectBased
1	10001	PART-A	Y
1	10002	PART-B	Y
2	10002	PART-C

I want to be able to take all of the ProjectBased values that are null and make them 'N'. I've tried a number of things without much luck, but I think I'm missing something obvious..

Thanks,

-Ken

Modifying the value of a Joined Column

swuehl — Mon, 21 Nov 2011 18:12:10 GMT

Not sure if this is the best solution, but you could probably do a resident load of your table and fill the missing values in, maybe like:

Orders:

LOAD * Inline

[

OrderNumber, OrderLine, ItemNumber

10001, 1, PART-A

10002, 1, PART-B

10002, 2, PART-C

];

MaterialDemands:

LOAD * Inline

[

OrderNumber, OrderLine, WorkOrder

10001, 1, 20000

10002, 1, 20001

];

Join (Orders)

LOAD

OrderNumber,

OrderLine,

'Y' AS ProjectBased

Resident MaterialDemands;

drop table MaterialDemands;

Result:

Noconcatenate LOAD OrderNumber, OrderLine, ItemNumber, if(ProjectBased='Y',ProjectBased,'N') as ProjectBased

resident Orders;

drop table Orders;

Hope this helps,

Stefan

Modifying the value of a Joined Column

Thu, 24 Nov 2011 18:08:13 GMT

I had been looking for a way to modify the data already loaded, but this does the trick.

Thanks!

-Ken