Modifying the value of a Joined Column

[#1]

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

[swuehl · Accepted solution]

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

[#3]

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

Thanks!

-Ken