Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sspringer
Partner - Contributor
Partner - Contributor

ApplyMap or Join Only If Null

How do I conditionally apply a map or join?

I have 2 tables, 1 is my master data which has 2 columns - 1 with an OfficeID and 1 with OfficeName.  Sometimes OfficeID is populated and other times OfficeName.  I have a second table (link table) that has the list of OfficeID's and corresponding names.  I want to apply a map or populate all of the NULL OfficeName records with the information from the second table.  What is the best way to accomplish this?

Thank you!

1 Solution

Accepted Solutions
Not applicable

Use this:

if (Trim(OfficeName)='', ApplyMap('Office_Map_by_id', Id),OfficeName) as OfficeName

It will work.

Regards,

Anosh

View solution in original post

5 Replies
Not applicable

Hi,

can office names be the same for different ids?

don't know is this the best way, but you can solve it with two mapping tables:

OFFICE:

LOAD * INLINE [

    ID, NAME

    1, office_1

    2, office_2

    3, office_3

    4, office_4

    5, office_5

    6, office_6

];

Office_Map_by_id:

Mapping LOAD

    ID, NAME Resident OFFICE;

Office_Map_by_name:

Mapping LOAD

     NAME, ID Resident OFFICE;

TEST:

LOAD * INLINE [

    F1, F2

    , office_1

    2,

    3,

    , office_4

    , office_5

    1,

    6,

    , office_5

];

TEST_RESULT:

LOAD if (IsNull(F1) or trim(F1)='', ApplyMap('Office_Map_by_name', F2, null()), F1) as result_id,

       if(IsNull(F2) or trim(F2)='', ApplyMap('Office_Map_by_id', F1, null()), F2) as result_name

    Resident TEST;

regards

jagannalla
Partner - Specialist III
Partner - Specialist III

If i understood your concept, you can achive this using mapping concept.

For example:

Office:

Load OfficeID, OfficeName from Office:

Map1:

Mapping OfficeID,OfficeName Resident Office;

Office2:

Load *, ApplyMap('Map1',OfficeID,Null()) as OfficeName

From LinkTable;

Not applicable

Hi sspringer,

Use isnull() clause to check where the office name is null and then use applymap to map it with office names as shown below in the load statement:

Load

if(isnull(office name)=-1, applymap())

Hope this help,

Anosh

sspringer
Partner - Contributor
Partner - Contributor
Author

I'm still not sure this solution works.  I tried this if(isnull(office name)=-1, applymap()) , however, if I use this and alias it as OfficeName, then I cannot also load OfficeName in the script above for rows that already have an OfficeName.  The problem is this:  I have a table with 20 rows, 10 rows already have OfficeName, but no OfficeID (those are fine).  10 rows have OfficeID but no OfficeName - I need to fill in those OfficeName's using a different table.

Any more ideas?  Thank you so much for responding!

Not applicable

Use this:

if (Trim(OfficeName)='', ApplyMap('Office_Map_by_id', Id),OfficeName) as OfficeName

It will work.

Regards,

Anosh