topic Re: Join not giving the desired results in QlikView

Join not giving the desired results

Tue, 05 Aug 2014 14:22:48 GMT

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

Re: Join not giving the desired results

m_woolf — Tue, 05 Aug 2014 15:27:09 GMT

I would expect that two tables would be joined on a common field.

You have 4 common fields.

Re: Join not giving the desired results

PrashantSangle — Tue, 05 Aug 2014 15:34:08 GMT

Hi,

Can you post sample data.

As you doing right join on the basis of 4 common field.

So it will gives you only those data which are present in resident table and corresponding value from table1

Which must give correct values

Check it and if possible share sample apps.

Regards

Re: Join not giving the desired results

jaimeaguilar — Tue, 05 Aug 2014 15:40:57 GMT

Hi,

I think that the problem is that all the fields are the same, so the join won't work. It sounds to me like you could achieve the result you want if you concatenate instead of joining. However, It would be better if you could post some sample file,

regards

Re: Join not giving the desired results

Tue, 05 Aug 2014 15:40:59 GMT

Thanks guys.. I just checked and I reckon the data types of the common fields is different.

Will try and change that and see how it goes.. will keep this post updated.

Thanks

Re: Join not giving the desired results

maxgro — Tue, 05 Aug 2014 15:42:05 GMT

may be you only need to join the first 3 fields? If so, rename sellingprice

table1:

load

month,

itemnumber,

week,

sellingprice as sellingpricetable1

from excel file

Re: Join not giving the desired results

Tue, 05 Aug 2014 15:55:07 GMT

Hmm.. the data type change did not help.

here is some sample data

excel data

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

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

so the difference is, there is value in SellingPrice column, and I want that in the resident data for itemnumber '123'

PS: Sellingprice is an inputfield.

Hope I am making some sense.

Thanks

Re: Join not giving the desired results

PrashantSangle — Tue, 05 Aug 2014 15:57:43 GMT

Hi,

rename one of the Selling price from table

Try like this,

table1:

load

month,

itemnumber,

week,

sellingprice

from excel file

right join (table1)

load

month,

itemnumber,

week,

sellingprice as old_selllinPrice

resident table3

Regards,

Re: Join not giving the desired results

Tue, 05 Aug 2014 15:58:10 GMT

Thanks for your reply, I tried that, but then it gives me two entries one with zero values from resident table and others with proper values from excel table, I tried distinct but doesnt help.

I think its considering all the values as unique.. but how and why?

Re: Join not giving the desired results

Tue, 05 Aug 2014 16:01:03 GMT

Thanks for the reply, but then this would create two separate columns, which I dont want 😞

Re: Join not giving the desired results

PrashantSangle — Tue, 05 Aug 2014 16:04:14 GMT

Hi,

After that script

Write

Final_table1:

Load

month,

itemnumber,

week,

sellingprice

resident table1

Drop table table1;

Hope this will help.

Regards

Re: Join not giving the desired results

martynlloyd — Tue, 05 Aug 2014 16:10:07 GMT

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;

Re: Join not giving the desired results

Tue, 05 Aug 2014 16:29:41 GMT

I tried this step but the values coming from table1 (excel table) are nulls. So the table contains '-' hypen.

Any help here?

Re: Join not giving the desired results

Tue, 05 Aug 2014 17:45:24 GMT

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?

Re: Join not giving the desired results

maxgro — Tue, 05 Aug 2014 18:04:05 GMT

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

Re: Join not giving the desired results

Tue, 05 Aug 2014 18:13:57 GMT

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;

Reference link

Re: Join not giving the desired results

martynlloyd — Wed, 06 Aug 2014 09:39:31 GMT

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)

Re: Join not giving the desired results

martynlloyd — Wed, 06 Aug 2014 09:48:23 GMT

BTW Pragati,

You did not mark your thread as a Question, so we don't get any points for helping you...

Re: Join not giving the desired results

Wed, 06 Aug 2014 10:20:31 GMT

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