topic Re: join between two qvd in QlikView

join between two qvd

geogou1973 — Thu, 05 Nov 2015 09:05:59 GMT

Hello.

I'm facing a problem with join two qvd files.

The one qvd file has 37000 records and the other has 14000 records.

I try to have all the records from the file of 37000 and in the same ITEM of the second file to take the changes.

i have the following script but i take only the records that matching in the field ITEM.

Can anyone help me in this.

LOAD

STKDAT,

FBPCS AS ITEM,

FGIMA,

QTY

FROM

STOCK_131.QVD (qvd);

LEFT JOIN

LOAD

INQTY,

OUTQTY,

ITEM,

STORE_ID

FROM

SRS_TABLE.QVD (qvd)

WHERE STORE_ID = 131;

Thank you in advance.

Re: join between two qvd

Mark_Little — Thu, 05 Nov 2015 09:09:34 GMT

Hi,

What is the problem you are having exactly?

Mark

Re: join between two qvd

jagan — Thu, 05 Nov 2015 09:27:54 GMT

Hi,

Try

LOAD

STKDAT,

FBPCS AS ITEM,

FGIMA,

QTY

FROM

STOCK_131.QVD (qvd);

FULL OUTER JOIN

LOAD

INQTY,

OUTQTY,

ITEM,

STORE_ID

FROM

SRS_TABLE.QVD (qvd)

WHERE STORE_ID = 131;

Re: join between two qvd

jonathandienst — Thu, 05 Nov 2015 09:33:08 GMT

Do you want to update the value of QTY?

Or simply load both tables? A join may not be necessary in this case.

Re: join between two qvd

geogou1973 — Thu, 05 Nov 2015 09:37:37 GMT

HELLO JAGAN.

FULL OUTER JOIN IS NOT CORRECT

Re: join between two qvd

HirisH_V7 — Thu, 05 Nov 2015 09:39:25 GMT

Hi,

Try making inner join . will let u to fetch the matched records only .

thanks,

Hirish

Re: join between two qvd

himanshi — Thu, 05 Nov 2015 09:41:23 GMT

hii,

try concatenate or join instead of left join.

Regards,

Himanshi Dubey

Re: join between two qvd

geogou1973 — Thu, 05 Nov 2015 09:42:56 GMT

Hello Jonathan.

Yes, in the same items i want to update the qty with the difference of inqty and outqty and i want to keep the QTY of the other items from the first table.

I'm doing the changes in the QTY in the Straight chart i have created in the qvw.

Re: join between two qvd

geogou1973 — Thu, 05 Nov 2015 09:44:55 GMT

Hello hirishv7.

I have the same results.

Re: join between two qvd

jonathandienst — Thu, 05 Nov 2015 09:48:50 GMT

Perhaps something like this then:

T_Data:

LOAD

STKDAT,

FBPCS AS ITEM,

FGIMA,

QTY

FROM

STOCK_131.QVD (qvd);

LEFT JOIN (T_Data)

LOAD

ITEM,

Sum(INQTY) - Sum(OUTQTY) As Move

FROM

SRS_TABLE.QVD (qvd)

WHERE STORE_ID = 131

GROUP BY ITEM

;

Data:

LOAD

STKDAT,

ITEM,

FGIMA,

QTY,

Alt(QTY, 0) + Alt(Move, 0) As FinalQTY

Resident T_Data:

DROP TABLE T_Data;

Re: join between two qvd

jonathandienst — Thu, 05 Nov 2015 09:51:22 GMT

Or this:

Map_Move:

Mapping LOAD

ITEM,

Sum(INQTY) - Sum(OUTQTY) As Move

FROM

SRS_TABLE.QVD (qvd)

WHERE STORE_ID = 131

GROUP BY ITEM

;

Data:

LOAD *,

RangeSum(QTY, Move) As FinalQTY

LOAD

STKDAT,

FBPCS AS ITEM,

FGIMA,

QTY,

ApplyMap('Map_Move', FBPCS, 0) As Move

FROM

STOCK_131.QVD (qvd);

Re: join between two qvd

Anonymous — Thu, 05 Nov 2015 09:54:58 GMT

Did not get you what exactly you want, but when it comes to join:

Table1

left join

Table2

Result: All the records from Table1 and common records from Table2

Table1

Right join

Table2

Result: All the records from Table2 and common records from Table1

Table1

Full join

Table2

Result: All the records from Table1 & Table2

Table1

inner join

Table2

Result: Common records from both the table

Hope this will clear you about Join!!

Re: join between two qvd

preminqlik — Thu, 05 Nov 2015 10:07:04 GMT

am not sure, but as per my understanding by your details, follow the below proce without joining

TABLE_1_37000:

LOAD

STKDAT,

FBPCS AS ITEM,

FGIMA,

QTY

FROM

STOCK_131.QVD (qvd);

TABLE_2_14000:

left keep (TABLE_1_37000)

LOAD

INQTY,

OUTQTY,

ITEM,

STORE_ID

FROM

SRS_TABLE.QVD (qvd)

WHERE STORE_ID = 131;

Re: join between two qvd

geogou1973 — Thu, 05 Nov 2015 10:34:55 GMT

Jonathan it is correct. Thank you very much.