topic Join on multiple fields in QlikView

Join on multiple fields

martijn — Thu, 02 Jul 2009 14:49:58 GMT

Hi,

I'm new to Qlikview. In the past I've developed a BusinessObjects universe and reports. I want to recreate these reports in Qlikview. I've come to the problem that I want to join a table with an other table on more than one field. In BO the join is:

Table1.FreeIntField_01=Table2.artgrp OR Table1.FreeIntField_02=Table2.artgrp OR Table1.FreeIntField_04=Table2.artgrp OR Table1.FreeIntField_05=Table2.artgrp.

I've tried to solve this issue with an concatenate load on Table1 where I rename the fields to artgrp. I'm not sure if this is the right way to solve this issue. Can somebody give me an advise?

Thanks in advance,

Regards,

Martijn

Join on multiple fields

johnw — Fri, 03 Jul 2009 00:37:21 GMT

There are probably a lot of different approaches. Which to take depends on your data and the MEANING of this join. Are fields 01, 02, 04, and 05 basically just an array? Why is 03 skipped?

On the surface at least, it looks similar to something we do here. A user can enter up to five defect codes on a particular item. In the source data, these are just separate columns in one row. In QlikView, we can turn these into multiple rows for a single column on a separate table. The items are linked to additional defect information through this new table.

So for example:

// Pretend these are your source data tables.
[Item Source Data]:
LOAD * INLINE [
Item, Status, Weight, Color, Defect1, Defect2, Defect3, Defect4, Defect5
...
];
[Defect Source Data]:
LOAD * INLINE [
Defect, Defect Severity, Defect Description
...
];

// This builds three linked tables from our two unlinked source tables.
[Item]:
LOAD Item, Status, Weight, Color
RESIDENT [Item Source Data]
;
[Item Defect]:
CROSSTABLE (Defect Sequence, Defect) // Crosstable makes one row per defect,
// keyed by Item and Defect Sequence = 'Defect1' etc.
// with the actual defect code in field "Defect"
LOAD Item, Defect1, Defect2, Defect3, Defect4, Defect5
RESIDENT [Item Source Data]
;
[Defect]:
NOCONCATENATE LOAD *
RESIDENT [Defect Source Data]
;

// Get rid of the source data.
DROP TABLES
[Item Source Data]
,[Defect Source Data]
;

Join on multiple fields

martijn — Fri, 03 Jul 2009 15:34:21 GMT

Thanks!!!!. This was the solution I was looking for!