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: 
Not applicable

Joining Tables with Blank Values

Hello Qlik Community,

    I would like to join Status in Table 2 to Table 1 based on the criteria in Table 2 where not blank.

If I use a left join Table 2 Code, Type, Model; status is only added to ID's 3 and 5.

If I use a left join Table 2 Code, Type; status is added to ID's 2, 3, 5, 7,9 (good) as well as ID 6 (bad).

      Additionally ID's 3 and 5 just happen to have the same status. I would expect issues if they were different.

Sorry for the poor synatx/short-hand. I hope I conveyed what I'm trying to accomplish.

Thanks for your help. Shaun.

Table1:

IDCodeTypeModel

1

Arthur

Tangerine

9
2ArthurCarrot
3WalterTangerine

9

4WalterCarrot
5WalterTangerine6
6WalterTangerine8
7CorrinaSprite4
8CorrinaTangerine9
9CorrinaSprite

Table2:

CodeTypeModelStatus
ArthurCarrotOpen
WalterTangerine9Closed
WalterTangerine6Closed
CorrinaSpriteOpen

Status of all others should be 'Pending', but I can live with a blank

Combined Table should look like:

IDCodeTypeModelStatus
1ArthurTangerine9Pending
2ArthurCarrotOpen
3WalterTangerine9Closed
4WalterCarrotPending
5WalterTangerine6Closed
6WalterTangerine8Pending
7CorrinaSprite4Open
8CorrinaTangerine9Pending
9CorrinaSpriteOpen
12 Replies
hector_munoz
Specialist
Specialist

Hi Shaun,

Try the following:

MAP_STATUS:

MAPPING LOAD Code & '|' & Type & '|' & Model     AS MAP_KEY

                           Status                                        AS MAP_DESC

RESIDENT TABLE2;

TABLE1:

LOAD .....

          .....

          ApplyMap('MAP_STATUS', Code & '|' & Type & '|' & Model, 'Pending')          AS Status

FROM xxxxx;

Regards,

H

Not applicable
Author

Thanks Héctor. That did it.

Not applicable
Author

New question. How can I use a wildmatch where blanks exist in Table2 (or should I be using something else)?

Basically, Table2 contains a blank, I don't care what that value is in Table1. I want Status based on only populated fields in Table2

For example, if ID 7 in Table1 has a Model = 4 whereas ID 9 has no Model, the Status in the resulting table would have ID 7 as Pending, but ID 9 as Open.

hector_munoz
Specialist
Specialist

Hi Shaun,

Then, if I donot miss anything, you should use an IF statement like:

If(Len(Model) = 0,

   'Pending',

   ApplyMap('MAP_STATUS', Code & '|' & Type & '|' & Model, 'Pending'))     AS Status

, here if a record has Model without information you always put "Pending" literal; in other case you use tha ApplyMap() function.

Hope it serves!

Regards,

H

annapochyla
Partner - Contributor III
Partner - Contributor III

You could try using second applymap function, that will ignore Model value from table1.

ApplyMap('MAP_STATUS', Code & '|' & Type & '|' & Model, ApplyMap('MAP_STATUS', Code & '|' & Type & '|', 'Pending') )          AS Status

Not applicable
Author

Thanks for the suggestions guys.

Héctor,

    Your first reply was spot on for that question. So  thanks for that help. I understand where you are going with your reply to my second question. However, if any field is blank in Table2, that means I don't care what the corresponding value in Table1 is. I can be any value or even blank. I hope that helps clarify what I'm trying to achieve.

Anna,

   Thanks for your reply. I think you understand what I'm trying to accomplish in my second question. I expect your solution will work in this situation. However, I anticipate in the future I will need to add more fields to Table1 and Table2. If I need to add 10 more fields, I would need to have multiple nested ApplyMap statements for every combination. I feel that there must be a more elegant solution to allow for expansion of the number of fields without, having an exponential grow in the size of my nested statements. Let me know if that doesn't make sense.

Thanks to both of you for your help, time and patience. Shaun.

hector_munoz
Specialist
Specialist

Hi Shaun,

If I have understood it well you only want those records coming from TABLE2 where they have not any empty or null value, have you? Then you could create MAP_STATUS table with a WHERE condition like below:

MAP_STATUS:

MAPPING LOAD Code & '|' & Type & '|' & Model     AS MAP_KEY

                           Status                                        AS MAP_DESC

RESIDENT TABLE2

WHERE      SubStringCount('|' & Code & '|' & Type & '|' & Model & '|', '||') = 0;

With this condition: SubStringCount('|' & Code & '|' & Type & '|' & Model & '|', '||') = 0 we will not take into account those records with two pipes in a row; and 2 pipes in a row using a pipe as a separator of key fields mean that any of the key fields is empty or null. I donot know if I can explain it propoerly...

Hope it serves!!!

Regards,
H

Not applicable
Author

Thanks Héctor. I think your code will exclude any rows from Table2 from the Map. That means that  ID in the Combined table will be mapped as Pending. I want to map based on any fields that are populated in Table2. If the field is not populated in Table2, I still want to map to Table1 but based only the remaining fields.

If I could Map Table 2 with a wildcard like * instead of null(), but I don't think we can map wildcards, correct?

I update the Tables in the origianl post to add a Model to ID 7. There will likely be more Fields added in the future, but for the items already mapped in Table2, those additional fields would also be mapped as * to not effect the prior results.

I don't think  I'm explainnig this very well. Sorry.

Kushal_Chawda

see this

Table2:

mapping LOAD trim(Code)&trim(Type) as Model ,

    Status

FROM

[https://community.qlik.com/thread/257637]

(html, codepage is 1252, embedded labels, table is @2)

where len(trim(Model))=0;

Table2_1:

mapping LOAD trim(Code)&trim(Type)&trim(Model) as Model ,

    Status

FROM

[https://community.qlik.com/thread/257637]

(html, codepage is 1252, embedded labels, table is @2)

where len(trim(Model))>0;

Table1:

LOAD *,

         if(Status1='Pending' and Status2<>'Pending' ,Status2,

          if(Status1<>'Pending' and Status2='Pending' ,Status1,Status2)) as Status;

LOAD ID,

    Code,

    Type,

    Model,

    applymap('Table2',trim(Code)&trim(Type),'Pending') as Status1,

    applymap('Table2_1',trim(Code)&trim(Type)&trim(Model),'Pending') as Status2

FROM

[https://community.qlik.com/thread/257637]

(html, codepage is 1252, embedded labels, table is @1);