Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to join two tables, one is loaded from an excel file and other is a resident.
the table structure and data looks like the this:
table1:
load
month,
itemnumber,
week,
sellingprice
from excel file
right join (table1)
load
month,
itemnumber,
week,
sellingprice
resident table3
I need all the rows from table3 and the equivalent values from table1.
with right join it contains all the rows from table3 but not the values.
Can you please help me or give me any idea, as to where I could be going wrong?#
Thanks
Hi,
After that script
Write
Final_table1:
Load
month,
itemnumber,
week,
sellingprice
resident table1
Drop table table1;
Hope this will help.
Regards
Extending Max's idea, load Table1 into memory and then use LEFT JOIN to join the tables
FinalTable
LOAD
...
Resident Table3
;
Left Join (FinalTable)
...
Resident Table1
;
Drop Table1;
I tried this step but the values coming from table1 (excel table) are nulls. So the table contains '-' hypen.
Any help here?
Just wanted to update you guys, I am still nowhere with this,
also I tried doing an inner join to see if I could get hold of just the common values, but to my surprise it returned nothing, Zero rows.. so this makes me believe that qlikview is thinking that all the columns are different though they have the same name.
Have you guys come across this?
if you do an inner join (join on 4 fields) you get 0 records because sellingprice is different (100 or 0)
excel_data:
load * inline [
month, itemnumber, week, sellingprice
Aug - 2014, 123, Week 31 - Aug, 100
Aug - 2014, 123, Week 32 - Aug, 100
Aug - 2014, 123, Week 33 - Aug, 100
Aug - 2014, 123, Week 34 - Aug, 100
Aug - 2014, 123, Week 35 - Aug, 100
];
//resident_table:
inner join (excel_data)
load * inline [
month, itemnumber, week, sellingprice
Aug - 2014, 123, Week 31 - Aug, 0
Aug - 2014, 123, Week 32 - Aug, 0
Aug - 2014, 123, Week 33 - Aug, 0
Aug - 2014, 123, Week 34 - Aug, 0
Aug - 2014, 123, Week 35 - Aug, 0
Aug - 2014, 1234, Week 31 - Aug, 0
Aug - 2014, 1234, Week 32 - Aug, 0
];
if you do a left join (field month, itemnumber, week, not sellingprice)
excel_data:
load * inline [
month, itemnumber, week, sellingprice
Aug - 2014, 123, Week 31 - Aug, 100
Aug - 2014, 123, Week 32 - Aug, 100
Aug - 2014, 123, Week 33 - Aug, 100
Aug - 2014, 123, Week 34 - Aug, 100
Aug - 2014, 123, Week 35 - Aug, 100
];
//resident_table:
left join (excel_data)
load * inline [
month, itemnumber, week, sellingprice2
Aug - 2014, 123, Week 31 - Aug, 0
Aug - 2014, 123, Week 32 - Aug, 0
Aug - 2014, 123, Week 33 - Aug, 0
Aug - 2014, 123, Week 34 - Aug, 0
Aug - 2014, 123, Week 35 - Aug, 0
Aug - 2014, 1234, Week 31 - Aug, 0
Aug - 2014, 1234, Week 32 - Aug, 0
];
you get
if you change left to right join, you get 7 records
Hi correct me if i wrong
1) it seems like all the column names are same it's generates synthetic keys
2) so rename(as) all the columns
3) To avoid null use Right keep
it maintain the data in individual tables
4) next do CONCATENATE (Table1) LOAD * RESIDENT Table2;
Hi Pragati,
You seem to be having some trouble with joins, I'm wondering if you need a different approach - maybe concatenate would work better in this situation, it would enable you to control things easier with IF logic:
table1temp:
load
month & itemnumber & week as T1Key,
month,
itemnumber,
week,
sellingprice as T1price
from excel file
Concatenate
load
'T3' as Type,
month & itemnumber & week as T3Key,
month,
itemnumber,
week,
sellingprice as T3price
resident table3
Table1:
Load
IF(..... .....) as ....
Resident table1temp
Where .....
BTW it is still not clear what you are trying to achieve - are there more fields that you are not telling us about - if you simply want to add the price, use
load
month & itemnumber & week as T3Key,
month,
itemnumber,
week,
sellingprice
resident table3
CONCATENATE
load
month,
itemnumber,
week,
sellingprice
from excel file
Where exists(T3Key, month & itemnumber & week)
BTW Pragati,
You did not mark your thread as a Question, so we don't get any points for helping you...
Hi,
Thanks for the heads up, I have opened this as a question now, with more information as to what I am trying to achieve.
Please have a look at that.
Problems joining two exactly same tables
Thanks