Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
marcelo_7
Contributor

JOIN and replace where exists

Hello,

I have a list of my total Orders. I will later be adding a column to show if they have been changed or not so to start I add it to begin with and set it to zero for all orders. Let's say it's like the below table.

ORDERS:

LOAD * INLINE [

    ORDER_NO, _CHANGED

    1, 0

    2, 0

    3, 0

    4, 0

     5, 0

];

I also have another table where I have orders that meet a certain criteria. They are in the table CHECK and some have been changed, others haven't.

CHECK:

LOAD * INLINE [

          ORDER_NO, _CHANGED

          1, 1

          2, 1

          3, 0

];

If I use left join the column _CHANGED will have the values that are in table ORDERS. I would like my result to have 1's where they have been changed and zeroes where they haven't. I would like to have the resulting table:

1, 1

2, 1

3, 0

4, 0

5, 0

How do I make that work using joins?

(I know one solution would be to create a different column and after a join add ( + ) both columns in a new column but I want to avoid loading it again below. Is it possible?)

Tags (2)
1 Solution

Accepted Solutions

Re: JOIN and replace where exists

In this case it should be quite simple. Use your CHECK table as a mapping table and use applymap

CHECK:

MAPPING LOAD * INLINE [

          ORDER_NO, _CHANGED

          1, 1

          2, 1

          3, 0

];

ORDERS:

LOAD 

ORDER_NO,

applymap('CHECK',ORDER_NO,_CHANGED) as _CHANGED

INLINE [

    ORDER_NO, _CHANGED

    1, 0

    2, 0

    3, 0

    4, 0

     5, 0

];


talk is cheap, supply exceeds demand
2 Replies

Re: JOIN and replace where exists

In this case it should be quite simple. Use your CHECK table as a mapping table and use applymap

CHECK:

MAPPING LOAD * INLINE [

          ORDER_NO, _CHANGED

          1, 1

          2, 1

          3, 0

];

ORDERS:

LOAD 

ORDER_NO,

applymap('CHECK',ORDER_NO,_CHANGED) as _CHANGED

INLINE [

    ORDER_NO, _CHANGED

    1, 0

    2, 0

    3, 0

    4, 0

     5, 0

];


talk is cheap, supply exceeds demand
marcelo_7
Contributor

Re: JOIN and replace where exists

Hello Gysbert,

I was stuck in my thinking and didn't realize I could use a map. Thanks for reminding me!

Cheers,

Marcelo

Community Browser