Join tables without combining them

[nlopedebarrios]

I have two tables, one with card transactions with card_number, date, measures, etc., and a Master Card Table, with card_number, etc. (not at all related with credit cards).

I'd like to make a report with the cards in the master table that have no transactions, filtering by period of time (from-to date). 

I tried left joining the tables, but Qlik Sense combines them in a one big table. In the other hand, if I don't configure the relation (by card_number), I read it's possible to get unrealistic results. 

I'm new to Qlik Sense, familiar with other products like Looker Data Studio and PowerBI.

How can I achieve  my goal?

[MatheusC]

Hi, @nlopedebarrios 

A guess maybe using mapping if you have a reference field to do this search in the mapping table using the applymap function to bring the value you need.


https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

Regarts,
Matheus

[nlopedebarrios]

I'm not sure if mapping would solve this, I'll take a look, thanks.

[RafaelBarrios · Accepted solution]

Hi @nlopedebarrios 

I think I have understood what you need and this would be the Keep script function, this works like a Join but without joining both tables into one.

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/combine-ta...

Example:  

In these examples we use the source tables Table1 and Table2:

Table1

A	B
1	aa
2	cc
3	ee

Table2

A	C
1	xx
4	yy

Left keep while reduce Table2 to existing A values in Table 1

VTab1:

SELECT * from Table1;

VTab2:

left keep SELECT * from Table2;

VTab1

A	B
1	aa
2	cc
3	ee

VTab2

A	C
1	xx

While left join result will be one table

VTable:

SELECT * from Table1;

left join SELECT * from Table2;

VTable

A	B	C
1	aa	xx
2	cc	-
3	ee	-

Note that Qlik connects tables by fields that have the same name.

Take this into account for Keep function but also for the final data model. If in the end there are tables that are connected by more than one field, you will have the so-called synthetic tables that you should avoid, this by making composite keys and renaming fields so that the tables are connected by a single field.

Hope this helps,

Best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!  😉

[marcus_sommer]

It sounds that there is no need to (classical) join both tables else to populate missing transactions in the fact-table. In some scenarios/requirements it's possible to display non-existing values without creating them - but they are seldom trivial and could produce serious side-effects. Therefore is a population of such data often the easier approach, maybe with something like (simplified):

t0: load distinct Cards from CardsDim; join(t0) load recno() as Month autogenerate 12;
t1: load Cards, Month, Cards & '|' & Month as Key resident t0;

t2: load *, Cards & '|' & Month as Key from CardsFacts;
concatenate(t2) load *, 0 as Value resident t1 where not exists(Key);