topic Re: JOIN and replace where exists in QlikView

JOIN and replace where exists

marcelo_7 — Tue, 23 Oct 2012 11:19:04 GMT

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?)

Re: JOIN and replace where exists

Gysbert_Wassenaar — Tue, 23 Oct 2012 11:22:18 GMT

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

];

Re: JOIN and replace where exists

marcelo_7 — Tue, 23 Oct 2012 11:27:34 GMT

Hello Gysbert,

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

Cheers,

Marcelo