Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Modifying the value of a Joined Column

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:

OrderLineOrderNumberItemNumberProjectBased
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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

2 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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


Thanks!

-Ken