topic Re: generate values after join in QlikView

generate values after join

surajap123 — Tue, 11 Nov 2014 08:39:04 GMT

Hi Experts,

I am doing qlikvie left join to add a column to the table.

Eg-

main:

id,

col2,

col3

from main

left join(main)

id,

if(col4='Y', col4, 'N') as col4

from tab;

Here the data coming from tab will always have a 'Y' as col4 value. So, for all the id's left joined to main table i want the col4 value as 'Y', otherwise 'N'(whenever the id present in tab is not in main table).

After left join i get 'Y' values for all id's that are matching in main and tab, but whenever the ids in tab is not in main then no values are populared. How to populate value 'N' for these rows?

Re: generate values after join

Anonymous — Tue, 11 Nov 2014 08:58:25 GMT

Saraj,

I will do it with a mapping table:

col4map:

LOAD id, if(col4='Y', 'Y', 'N') as col4

FROM ...;

Main:

LOAD id, col2, col3,

ApplyMap('col4map', id, 'N') as col4 ;

FROM ...;

Marc.

Re: generate values after join

Tue, 11 Nov 2014 09:13:00 GMT

Hi Saraj,

Try the script below

[main]:

Load * Inline

[

id,col2,col3

1,col2,col3

];

[test]:

Join ([main])

Load

If(Exists(id),'Y','N') AS col4

;

Load * Inline

[

id,

];

Re: generate values after join

PrashantSangle — Tue, 11 Nov 2014 09:18:24 GMT

Hi,

Try below code,

main:

id,

col2,

col3

from main

left join(main)

id,

if(col4='Y', col4, 'N') as col4

from tab;

Final_Main:

load id,

col2,

col3,

if(isNull(col4),'N',col4) as New_col4

Resident main;

Drop tables main;

Regards