Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

microwin88x
Contributor III

Solved

Solved

1 Solution

Accepted Solutions

Re: Fill Null Values from Other Table

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;

View solution in original post

6 Replies
karthikeyan1504
Contributor III

Re: Fill Null Values from Other Table

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.

microwin88x
Contributor III

Re: Fill Null Values from Other Table

Thanks, but with that option I get the following:

DOC_NUMDOC_YEARACTIVITYCENTERSTATUSREGION
001201300001100CNORTH
002201500500200FSOUTH
004201400010300OWEST
00120140001050--
003201300030150--
005201500001300--

Instead of:

DOC_NUMDOC_YEARACTIVITYCENTERSTATUSREGION
001201300001100CNORTH
00120140001050ONORTH
002201500500200FSOUTH
003201300030150CEAST
004201400010300OWEST
005201500001300WWEST
karthikeyan1504
Contributor III

Re: Fill Null Values from Other Table

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

Re: Fill Null Values from Other Table

Use ApplyMap to bring back your missing values

Don't join - use Applymap instead

Re: Fill Null Values from Other Table

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;

View solution in original post

Re: Fill Null Values from Other Table

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;