Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Not understood your exact requirements... Can you please elaborate little more?
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
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
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
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
Hi Stefan
Thanks for that. Perfectly correct. I didn't think I needed to add the Mkt_Cd. Learn a little every day.
Cheers