topic Re: Join Conundrum in QlikView

Join Conundrum

Sat, 11 Oct 2014 10:59:59 GMT

Hi - this is my first post and am I just getting into the QlikView world!!! Just exploring what I can do with this to help reconcile some project numbers based on existing files.

I am a little confused as to why why join is essentially doubling my test file. I have two tables, tmpData and Mkt_Filt.

Both tables have a common field - Mkt_Cd, and both have the 5100 and 5336 values, but when I join the two resident tables, it doubles the transactions.

My tmpData table has the following relevent fields:

Mkt_Cd	Mkt_Desc	Yr	MonthText	GBPVal
5100	GB	2014	@1	10000
5336	Ireland	2014	@1	3000

My Mkt_Filt table has:

Mkt_Cd	Start_Date
5100	01/01/2014
5336	01/06/2014

Target Final Table

Mkt_Cd	Start_Date (from Mkt_Filt)	GBPVal
5100	01/01/2014	10000
5336	01/06/2014	3000

But I get - both dates showin but only 1 is relevent to the particular market.

Mkt_Cd	Start_Date	GBPVal
5100	01/01/2014	10000
5100	01/06/2014	10000
5336	01/01/2014	3000
5336	01/06/2014	3000

Any help would be appreciated. I have uploaded my qvw and the initial spreadsheet.

Thanks

Les

Re: Join Conundrum

MK_QSL — Sat, 11 Oct 2014 11:13:37 GMT

Not understood your exact requirements... Can you please elaborate little more?

Re: Join Conundrum

Sat, 11 Oct 2014 11:25:10 GMT

Hi, basically why does the Start_Date 01/06/2014 show up against Mkt_Cd 5100, when it is only relevent to 5336, and vice versa, why does the Start_Date 01/01/2014 show against Mkt_Cd 5336 when it is only valid for 5100.

I am expecting the 3rd table above to be my final table, not the 4th table.

HTH

thanks

Les

Re: Join Conundrum

Anonymous — Sat, 11 Oct 2014 11:27:54 GMT

Hi,

your data will double when the table you join has values which are not distinct. For example you have two occurences of 5100 in the table you join, then you will also get 2 occurences in the table you joined the former table. You could try load distinct on the table you want to join.

Is it possible that the two fields on which the join is performed have different names? This would cause a carthesian product and that is what your results look like.

Best regards

Stefan

Re: Re: Join Conundrum

Sat, 11 Oct 2014 13:19:27 GMT

Hi - Sorry I was called away.

The Mkt_Filt table only has the two entries - the Mkt_Cd being unique. The fields look exactly the same to me - same case, same spelling.

Can you see anything wrong with this?

Directory;

// Bring in Mkt_Filt table - this only has 2 lines - Mkt_Cd is unique

Mkt_Filt:

LOAD

Mkt_Cd,

Start_Date

FROM

[2014_crosstable.xlsx]

(ooxml, embedded labels, table is Sheet2);

// Bring in Cross table - - this only has 2 lines - Mkt_Cd is unique, and matches values in Mkt_Filt table

tmpData:

Crosstable (MonthText,GBPVal,4)

Load Mkt_Cd,Mkt_Description,Measure,Left(FileName(),4) as Yr,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17

FROM [2014_crosstable.xlsx]

(ooxml, embedded labels, table is Sheet1);

// Make changes to imported crosstable - specifically Mth and TranDate

tmpData2:

Load

Mkt_Cd,

Measure,

Yr,

Replace(MonthText,'@','')-5 as Mth,

MakeDate(Yr,Replace(MonthText,'@','')-5,1) as TranDate,

GBPVal

Resident tmpData;

// Drop initial tmp table

Drop Table tmpData;

// Bring in start_date to tmpData2 table - basically just want to add the Start_Date field to the tmpData2 table

INNER JOIN (tmpData2)

Load Start_Date

Resident Mkt_Filt;

// Drop Mkt_Filt table

drop table Mkt_Filt;

Thanks Les

Re: Join Conundrum

Anonymous — Sat, 11 Oct 2014 13:47:15 GMT

Hi,

in your inner join you are missing

Mkt_Cd,

this causes the carthesian product of both tables. So your load statement should look like:

INNER JOIN (tmpData2)

Load

Mkt_Cd,

Start_Date

Resident Mkt_Filt;

Best regards

Stefan

Re: Re: Join Conundrum

Sat, 11 Oct 2014 13:50:57 GMT

Hi Stefan

Thanks for that. Perfectly correct. I didn't think I needed to add the Mkt_Cd. Learn a little every day.

Cheers