topic Re: join in QlikView

join

denis115 — Wed, 25 Nov 2020 16:16:04 GMT

Hello! I've got a problem! help me please.

I want to join 2 tables ,they both have same fields the only one difference is place FROM where it exctracting those files.

-----Table 1 :

Prime_Price:
select
rownrs,
prodid as "ИД ТМЦ",
DOCDATE as "Дата",
StoreID as "ИД Место хранение",
QTY as "Кол-во себ.",
PRICE_COST as "Пр. По Прайсу",
PRIME_COST as "Пр. Себес.",
DOC_COST as "Пр. по Чеку",
DOCTYPE as "Тип Докум."
from
sm_sales;

----Table 2 :

//same fields like "Table 1"

from sm_purch;

the error is : column ambiguously defined

THANK YOU !

Re: join

tripatirao — Wed, 27 Sep 2017 11:21:09 GMT

use Concatenate between 2 table.

Re: join

Clever_Anjos — Wed, 27 Sep 2017 11:22:20 GMT

"column ambiguously defined" seems to be an Oracle error message.

Would you mind attaching your 2nd SQL?

Re: join

denis115 — Wed, 27 Sep 2017 11:51:30 GMT

I already used concatination in each tab, for each select I have one tab and in each tab I used concatination for optimization, what do I need to do?

Re: join

YoussefBelloum — Wed, 27 Sep 2017 12:07:37 GMT

Hi,

Maybe try to use [ ] instead od " " for the column renaming.

Regards,

Re: join

eduardo_dimperio — Wed, 27 Sep 2017 13:27:28 GMT

Hi Denis,

Did you use the same name table (Prime_Price) to both extract?

If not try this:

First do some tests, to be sure about your select.

1 - Select Table 1 (only)

2 - Select Table 2 (only)

3- if you succeed, extract both

4- If you succeed, use the code below

Table_1:
select
rownrs,
prodid as "ИД ТМЦ",
DOCDATE as "Дата",
StoreID as "ИД Место хранение",
QTY as "Кол-во себ.",
PRICE_COST as "Пр. По Прайсу",
PRIME_COST as "Пр. Себес.",
DOC_COST as "Пр. по Чеку",
DOCTYPE as "Тип Докум."
from
sm_sales;

Left Join (Table_1)

//table_2:

select

rownrs,

prodid as "ИД ТМЦ",

DOCDATE as "Дата",

StoreID as "ИД Место хранение",

QTY as "Кол-во себ.",

PRICE_COST as "Пр. По Прайсу",

PRIME_COST as "Пр. Себес.",

DOC_COST as "Пр. по Чеку",

DOCTYPE as "Тип Докум."

from sm_purch;

5- If you get the error, comment all fields and uncomment one by one until find the problem (i know thats a dumb test but works)

Re: join

denis115 — Thu, 28 Sep 2017 05:24:30 GMT

and if I use concatination, I need to use it on both tables? I mean I need do write it 2 times? and also about inner join?

Re: join

avinashelite — Thu, 28 Sep 2017 05:41:30 GMT

share you complete script !! so that we could suggest the solution

Re: join

denis115 — Thu, 28 Sep 2017 05:42:26 GMT

error cant find table 2 :

Concatenate (Prime_Purchase) LOAD

ROWNRS

,"ИД ТМЦ"

, "Дата"

,"ИД Место хранение"

,"Кол-во себ."

,"Пр. По Прайсу"

,"Пр. Себес."

,"Пр. по Чеку"

,"Тип Докум."

from Prime_Purchase.qvd (qvd)

where not exists (ROWNRS)

Re: join

PrashantSangle — Thu, 28 Sep 2017 05:46:11 GMT

can you post complete script??

Regards,

Re: join

denis115 — Thu, 28 Sep 2017 06:05:25 GMT

--------------------TABLE 1

Prime_Price:
select
rownrs,
prodid as "ИД ТМЦ",
DOCDATE as "Дата",
StoreID as "ИД Место хранение",
QTY as "Кол-во себ.",
PRICE_COST as "Пр. По Прайсу",
PRIME_COST as "Пр. Себес.",
DOC_COST as "Пр. по Чеку",
DOCTYPE as "Тип Докум."
from
vbnz.sm_sales_with_prime_price
where docdate>='01.JAN.2015';
//where docdate >= '$(BeginningThisExecTime)' ;

Concatenate (Prime_Price) LOAD
ROWNRS
,"ИД ТМЦ"
, "Дата"
,"ИД Место хранение"
,"Кол-во себ."
,"Пр. По Прайсу"
,"Пр. Себес."
,"Пр. по Чеку"
,"Тип Докум."
from Prime_Price.qvd (qvd)
where not exists (ROWNRS) ;

Inner Join
sql
select ROWNRS
from vbnz.sm_purch_with_prime_price
where docdate >= '01.JAN.2015' ;

STORE Prime_Price into Prime_Price.qvd;

------------------TABLE 2

Prime_Purchase:
select
ROWNRS,
PRODID as "ИД ТМЦ",
DOCDATE as "Дата",
STOREID as "ИД Место хранение",
QTY as "Кол-во себ.",
PRICE_COST as "Пр. По Прайсу",
PRIME_COST as "Пр. Себес.",
DOC_COST as "Пр. по Чеку",
DOCTYPE as "Тип Докум."
from
vbnz.sm_purch_with_prime_price
//where docdate>='01.JAN.2015';
where docdate >= '01.JAN.2015' ;

Concatenate (Prime_Purchase) LOAD
ROWNRS
,"ИД ТМЦ"
, "Дата"
,"ИД Место хранение"
,"Кол-во себ."
,"Пр. По Прайсу"
,"Пр. Себес."
,"Пр. по Чеку"
,"Тип Докум."
from Prime_Purchase.qvd (qvd)
where not exists (ROWNRS) ;

Inner Join
sql
select ROWNRS
from vbnz.sm_purch_with_prime_price
where docdate >= '01.JAN.2015' ;

STORE Prime_Purchase into Prime_Purchase.qvd;

Re: join

PrashantSangle — Thu, 28 Sep 2017 06:27:09 GMT

Hi,

Use noconcatenate before

Prime_Pruchase:

Select...

Regards,

Prashant

Re: join

denis115 — Thu, 28 Sep 2017 07:11:28 GMT

Thank you! I thing that's what I need.

Re: join

eduardo_dimperio — Thu, 28 Sep 2017 13:10:10 GMT

Hi Denis, i saw that you solve your problem, but just answer you.

Yes, if you use concatenate you need both tables, to concatenate one in another, cause the compiler need to know what input you'll use (what tables in that case). In your case you can use inner join, case all fields are the same.

But i recomend this material to better understand the concept (i use all the time )

Understanding Join, Keep and Concatenate

Good Luck