Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
ID | Code | Type | Model |
---|---|---|---|
1 | Arthur | Tangerine | 9 |
2 | Arthur | Carrot | |
3 | Walter | Tangerine | 9 |
4 | Walter | Carrot | |
5 | Walter | Tangerine | 6 |
6 | Walter | Tangerine | 8 |
7 | Corrina | Sprite | 4 |
8 | Corrina | Tangerine | 9 |
9 | Corrina | Sprite |
Table2:
Code | Type | Model | Status |
---|---|---|---|
Arthur | Carrot | Open | |
Walter | Tangerine | 9 | Closed |
Walter | Tangerine | 6 | Closed |
Corrina | Sprite | Open |
Status of all others should be 'Pending', but I can live with a blank
Combined Table should look like:
ID | Code | Type | Model | Status |
---|---|---|---|---|
1 | Arthur | Tangerine | 9 | Pending |
2 | Arthur | Carrot | Open | |
3 | Walter | Tangerine | 9 | Closed |
4 | Walter | Carrot | Pending | |
5 | Walter | Tangerine | 6 | Closed |
6 | Walter | Tangerine | 8 | Pending |
7 | Corrina | Sprite | 4 | Open |
8 | Corrina | Tangerine | 9 | Pending |
9 | Corrina | Sprite | Open |
Thanks Kushal Chawda. That works. In a case where I 'filter' based on perhaps only one field, I would think I would have to created additional variants of your mapping load and applymap, correct? For example, if Table1 and Table2 were shown as shown below, to create the combined table your script would also add the following.
This seems lik the script could quickly grow larger as the number of fields in Table2 increases. If I had 10,000 IDs and 8 fields in Table2 that may or may not be blank, I would need to add a new mapping Load, apply map and if statements for each cobination of those 8 fields, correct?
One more question, is there a particular reason you used trim? Is that a best practice or something else? Just curious.
Thanks again. Shaun.
Table2:
...
Table2_1:
...
Table2_2:
mapping LOAD trim(Code) as Model ,
Status
FROM...
where len(trim(Model))=0 and len(trim(Type))=0;
Table1:
LOAD *,
if(Status3<>'Pending',Status3,
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
applymap('Table2_2',trim(Code),'Pending') as Status3
FROM
Table1:
ID | Code | Type | Model |
---|---|---|---|
1 | Arthur | Tangerine | 9 |
2 | Arthur | Carrot | |
3 | Walter | Tangerine | 9 |
4 | Walter | Carrot | |
5 | Walter | Tangerine | 6 |
6 | Walter | Tangerine | 8 |
7 | Corrina | Sprite | 4 |
8 | Corrina | Tangerine | 9 |
9 | Corrina | Sprite | |
10 | Victor | Carrot | |
11 | Victor | Tangerine | 8 |
12 | Victor | Sprite | 4 |
13 | Victor | Sprite |
Table2:
Code | Type | Model | Status |
---|---|---|---|
Arthur | Carrot | Open | |
Walter | Tangerine | 9 | Closed |
Walter | Tangerine | 6 | Closed |
Corrina | Sprite | Open | |
Victor | Closed |
Combined Table:
ID | Code | Type | Model | Status |
---|---|---|---|---|
1 | Arthur | Tangerine | 9 | Pending |
2 | Arthur | Carrot | Open | |
3 | Walter | Tangerine | 9 | Closed |
4 | Walter | Carrot | Pending | |
5 | Walter | Tangerine | 6 | Closed |
6 | Walter | Tangerine | 8 | Pending |
7 | Corrina | Sprite | 4 | Open |
8 | Corrina | Tangerine | 9 | Pending |
9 | Corrina | Sprite | Open | |
10 | Victor | Carrot | Closed | |
11 | Victor | Tangerine | 8 | Closed |
12 | Victor | Sprite | 4 | Closed |
13 | Victor | Sprite | Closed |
Number of IDs would not make a difference until you have very large data set. Also in your case, there will be a fix key like code, model and type based on which you would do the mapping so number of fields also doesn't matter.
Thanks Kushal Chawda. It is clear that number of IDs doesn't matter.I only mention that because as the number of IDs increases, the number of fields I will want to sort by will also increase. Table2 (where I'm pulling my Status map) could easily end up having 200+ rows with 20 different fields to sort on. Each row will have a different mixture of sorting fields.
For the example with the current fields, the script only requires the following field combinations for 1) Map Loading, 2)nested if statements and 3) apply map:
-Code where Type & Model are null
-Code & Type where Model is null
-Code, Type & Model where none are null
The number of possible combinations will at a minumum be squared, then multiply that again for each variant within each field. I'm not saying this is the case, but it could grow there.
If I add more fields and combinations it can easily exapnd to:
-Code where (Type, Model, Color, Size, Brand, Location, Season) are null()
-Code & Type where (Model, Color, Size, Brand, Location, Season) are null()
-Code, & Model where (Type, Color, Size, Brand, Location, Season) are null()
-Code & Color where (Type, Model, Size, Brand, Location, Season) are null()
-Code, Brand, & Location where (Type, Model, Color, Size, Season) are null()
-Code, Model, & Season where (Type, Color, Size, Brand, Location) are null()
-Code, Size where (Type, Model, Color, Size, Brand, Location, Season) are null()
-Code, Brand, & Size where (Type, Model, Color, Location, Season) are null()
-Code, Color, Location where (Type, Model, Size, Brand, Season) are null()
-Code, Type, Model & Season where (Color, Size, Brand, Location) are null()
...
In that case, I would have to have 10 Map load scripts, 10 nested if statements, 10 applymap functions.
For each addition field combination added to Table2, I would need to revise the script and add that new field combination to the script.
Perhaps this is turning in to a string issue instead? I'm just throwing ideas out there. Thanks again for your help. Shaun