Unmatched or Nulls in qliksense

[vik9]

Hi All,

I have two tables, in which there are unmatched records after association. How do i handle unmatched records after association between two tables. I want to map these unmatched records to one of the dimension value.

I implemented below approach 

T1:
Load * Inline
[
ID,Val
1,10
2,20
3,50
4,100
];
left Join
T2:
Load * Inline [
ID,City
1,Mum
2,Che
3,Bnglr
]
;

NoConcatenate
T3:
load ID,Val,
if(IsNull(City),'Bnglr',City) as City
Resident T1;
Drop Table T1;

My problem after implementing this i feel dashboard is slow. Is there any other alternative to overcome this scenario with out using left join

[rubenmarin]

Hi, I don't see why it would have an impact on performance, maybe you real scenario is creating unexpected relationships or causing duplicates.

Check the data model to confirm the relations between tables and that the number of rows of T3 is the same than it was on T1.

[vik9]

Yes I am using link table in the data model

[vik9]

is there any other work around other than left join.

[rubenmarin]

Another way to do this is using a mapping table: https://help.qlik.com/es-ES/sense/May2021/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunction...

[vik9]

I have nulls in fact which have no associations in dimensional table. I am not sure how Applymap() will work here.

[rubenmarin]

The 3rd parameter of applymap is the value when no association is found, you can also use the If(IsNull().

But as said it the first post, I don't see why this relationship will have an impact on performance. Post the final data model of this relations with the link table before and after appying this change to have an overall view on what is happening.

Also check that the number of rows not increases.

[vik9]

I dont have the similar Column in T1 and T2 apart from ID.

Can you let me know how this can be achieved using ApplyMap(). 

My requirment is simple if i select all the values in City it should sum all the values in table including Null. 

But in the below screenshot it is not the case.However if i select individual city it is matching with raw data 

[rubenmarin]

The join you are doing is done by ID, so to replicate that the same field must be used. Adnd you are using If(IsNull(... to assign Bnglr so the same can be done using the 3rd parameter of applymap.

mapCity:
Mapping Load * Inline [
ID,City
1,Mum
2,Che
3,Bnglr
]

T1:
Load *, ApplyMap('mapCity',ID,'Bnglr') as City  Inline
[
ID,Val
1,10
2,20
3,50
4,100
];

[vik9]

But i dont want to add City field to metric table. City field must be from T2 (the dimension table). city filter should be from  Dim table.