generate values after join

[surajap123]

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?

[Anonymous · Accepted solution]

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.

[#3]

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,

  1

  2

];

[PrashantSangle]

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