Dimension mapping table

[Scott2]

 Hi Qlik Community

Please can you help with the following query. 

I have a Fact table that contains the following columns (and more..). 

TradeId, SystemName

1,SystemX

2,SystemY

3,SystemX,SystemY

As you can see the last trade is in more than one system. I would like to have a dimension filter that users can filter on that is the unique System Name e.g. would be a drop down list containing unique values of "SystemX" and "SystemY" and would not contain a value in the drop down of "SystemX,SystemY". When the user selects either SystemX or SystemY then TradeId = 3 would be shown (As well as Trade 1 or 2 dependent on what System was selected).

Please can you advise the best way to achieve this? If i create a mapping table mapping "SystemX,SystemY" to "SystemX" and "SystemX,SystemY" to "SystemY" I end up with two rows for TradeId = 3 as it does an outer join on my fact table and mapping table.

thanks!

[Mark_Little]

Hi,

The only way i can think will be a table that links on the TradeID, so the Table1

TradeId, .....

1,

2,

3

Table2

1, SystemX

2, SystemY

3, SystemX

3,SystemY.

I would Probably approach it Something like below

Temp

Load

TradeId,

SUBFIELD(SystemName,',',0) as Field 1

SUBFIELD(SystemName,',',1) as Field 2

From ....;

NOCONCATENATE

LOAD

TradeID,

FIELD1 as SystemName

Resident Temp;

Concatenate

LOAD

TradeID,

FIELD2 as SystemName

Resident Temp

WHERE LEN(Field2)>0;

[marcus_sommer]

A "normal" dimension table should work for your described scenario, like:

DimSystem:
load *, subfield(SystemName, ',') as SystemNameDimension;
load text(fieldvalue('SystemName', recno())) as SystemName
autogenerate fieldvaluecount('SystemName');

and the SystemNameDimension is used for the selection.

[Scott2]

Hi Marcus, thanks for your reply. I had originally tried this (and just re-tried it) but as Qlik does an outer join between the Fact table and the new DimSystem table it means that when there is no SystemNameDimension selected then my PivotTable sees two rows instead of 1 . I can work around this by doing a count distinct in my Pivot Table data but really don't like the idea of the outer join as it may cause issues elsewhere. Any other suggestions on how to approach this? thanks

[Scott2]

Hi Mark, thanks for your reply. Unfortunately, unless I am mistaken this will still cause Qlik to do an outer join across the link table leading to 2 rows instead of 1 for TradeId=3. Any other ideas most welcome 🙂 

[marcus_sommer]

I couldn't comprehend your results in creating extra rows. Please share in more details how the pivot looks like - dimensions + expressions and how the tables are associated in the data-model.

Before you may check the data itself - means adding an unique identifier like recno() and/or rowno() in the load and using then all relevant fields + the identifier in a table-box to see what is really loaded (all Qlik objects are showing always the distinct (combination) of field-values - you won't see any duplicates unless by the aggregation-results which would of course consider all records).