topic Re: Fill Null Values from Other Table in QlikView

Solved

microwin88x — Mon, 28 Sep 2015 03:51:45 GMT

Solved

Re: Fill Null Values from Other Table

karthikeyan1504 — Mon, 28 Sep 2015 03:56:21 GMT

Hi,

you can follow the below method via script,

Main_Values:

Load *

from Main_Values where not isnull(center);

Concatenate(Main_Values)

Load *

from Additional_Values;

Hope this helps!!!

Warm Regards,

Karthikeyan.

Re: Fill Null Values from Other Table

microwin88x — Mon, 28 Sep 2015 04:07:11 GMT

Thanks, but with that option I get the following:

DOC_NUM	DOC_YEAR	ACTIVITY	CENTER	STATUS	REGION
001	2013	00001	100	C	NORTH
002	2015	00500	200	F	SOUTH
004	2014	00010	300	O	WEST
001	2014	00010	50	-	-
003	2013	00030	150	-	-
005	2015	00001	300	-	-

Instead of:

DOC_NUM	DOC_YEAR	ACTIVITY	CENTER	STATUS	REGION
001	2013	00001	100	C	NORTH
001	2014	00010	50	O	NORTH
002	2015	00500	200	F	SOUTH
003	2013	00030	150	C	EAST
004	2014	00010	300	O	WEST
005	2015	00001	300	W	WEST

Re: Fill Null Values from Other Table

karthikeyan1504 — Mon, 28 Sep 2015 04:32:04 GMT

Could you please let me know the relation/link between status, region & Doc_Num, Doc_Year?

Re: Fill Null Values from Other Table

robert_mika — Mon, 28 Sep 2015 04:42:07 GMT

Use ApplyMap to bring back your missing values

Don't join - use Applymap instead

Re: Fill Null Values from Other Table

settu_periasamy — Mon, 28 Sep 2015 04:44:47 GMT

Hi,

May be like this..

T1:

LOAD * INLINE [

DOC_NUM, DOC_YEAR, ACTIVITY, CENTER, STATUS, REGION

1, 2013, 1, 100, C, NORTH

1, 2014, 10, -, O, NORTH

2, 2015, 500, 200, F, SOUTH

3, 2013, 30, -, C, EAST

4, 2014, 10, 300, O, WEST

5, 2015, 1, -, W, WEST

];

Left Join(T1)

LOAD DOC_NUM, DOC_YEAR, ACTIVITY, CENTER as CENTER1;

LOAD * INLINE [

DOC_NUM, DOC_YEAR, ACTIVITY, CENTER

1, 2014, 10, 50

3, 2013, 30, 150

5, 2015, 1, 300

] ;

NoConcatenate

T2:

Load DOC_NUM,

DOC_YEAR,

ACTIVITY,

if(CENTER='-',CENTER1,CENTER) as CENTER,

STATUS,

REGION Resident T1 ;

DROP Table T1;

Re: Fill Null Values from Other Table

settu_periasamy — Mon, 28 Sep 2015 04:51:59 GMT

Alternate Solution using ApplyMap:

Mapping:

Mapping LOAD DOC_NUM&DOC_YEAR&ACTIVITY as Key, CENTER;

LOAD * INLINE [

DOC_NUM, DOC_YEAR, ACTIVITY, CENTER

1, 2014, 10, 50

3, 2013, 30, 150

5, 2015, 1, 300

] ;

T1:

Load *,DOC_NUM&DOC_YEAR&ACTIVITY as Key;

LOAD * INLINE [

DOC_NUM, DOC_YEAR, ACTIVITY, CENTER, STATUS, REGION

1, 2013, 1, 100, C, NORTH

1, 2014, 10, -, O, NORTH

2, 2015, 500, 200, F, SOUTH

3, 2013, 30, -, C, EAST

4, 2014, 10, 300, O, WEST

5, 2015, 1, -, W, WEST

];

NoConcatenate

T2:

LOAD DOC_NUM,

DOC_YEAR,

ACTIVITY,

If(CENTER='-',ApplyMap('Mapping',Key),CENTER) as CENTER,

STATUS,

REGION

Resident T1;

DROP Table T1;