topic Re: need to left join sql table in qlikview in QlikView

need to left join sql table in qlikview

Mon, 22 Dec 2014 10:08:22 GMT

hi,

i have VBRK table and VBRP table of SAP

I have fetched data from VBRK table for 1 year on fkdat field.

now I need to fetch data from VBRP table on VBELN field using VBRK tables FKDAT.

so I need to do left join on the basis of VBELN FEILD BETWEEN BOTH TABLE(VBRK and VBRP).

NEED HELP IN PERFORMING LEFT JOIN IN SQL ON QLIKVIEW SCRIPT BETWEEN VBRK AND VBRP TABLE.

THANKS

Re: need to left join sql table in qlikview

Gysbert_Wassenaar — Mon, 22 Dec 2014 10:11:47 GMT

VBRK:

SQL SELECT VBELN, ...other fields... FROM VBRK;

left join(VBRK)

SQL SELECT VBELN, ...other fields... FROM VBRP;

Re: need to left join sql table in qlikview

anbu1984 — Mon, 22 Dec 2014 10:15:29 GMT

SQL SELECT VBRK.Col1,..,VBRP.Col2 FROM VBRK Left outer join VBRP

On VBRK.Key1 = VBRP.Key1 And VBRK.Key2 = VBRP.Key2

Re: need to left join sql table in qlikview

Mon, 22 Dec 2014 10:34:13 GMT

Hi ,

I am using this script for fetching the data

LOAD

VBELN ,

POSNR ,

PRSDT ,

FKIMG ,

PSTYV ,

AUFNR ,

GSBER ,

NETWR ,

AUBEL ,

AUPOS ,

VKGRP ,

MATNR ,

PMATN ,

KUNDE ,

PARVW ,

MANDT ,

VTWEG ,

FKART ,

FKTYP ,

VBTYP ,

VKORG ,

BZIRK ,

KNUMV ,

FKDAT ,

KDGRP ,

KUNRG ,

KUNAG ,

SPART ,

ERDAT ,

AEDAT ;

SQL SELECT

VBRP~VBELN

VBRP~POSNR

VBRP~PRSDT

VBRP~FKIMG

VBRP~PSTYV

VBRP~AUFNR

VBRP~GSBER

VBRP~NETWR

VBRP~AUBEL

VBRP~AUPOS

VBRP~VKGRP

VBRP~MATNR

VBRP~PMATN

VBRK~MANDT

VBRK~VTWEG

VBRK~FKART

VBRK~FKTYP

VBRK~VBTYP

VBRK~VKORG

VBRK~BZIRK

VBRK~KNUMV

VBRK~FKDAT

VBRK~KDGRP

VBRK~KUNRG

VBRK~KUNAG

VBRK~SPART

VBRK~ERDAT

VBRK~AEDAT

from VBRK left join VBRP on ( VBRK~VBELN = VBRP~VBELN )

where VBRK~FKDAT=< '$(varDate)'.

You can set the value of variable as per your requirement. Hope this works for you.

Regards

Re: need to left join sql table in qlikview

Mon, 22 Dec 2014 12:18:42 GMT

hi,

I Have this script.

Plz tell me where I am wrong.

let vFromDATE = '20130101';
let vToDATE= '20131231';

[VBRK]:

Load


   [VBELN] as [Billing Document_VBELN.VBRK],
   [FKDAT] as [Billing date_FKDAT.VBRK];

SQL Select VBELN FKDAT from VBRK

WHERE FKDAT >= '$(vFromDATE)'and FKDAT <= '$(vToDATE)'
;

Left join([VBRK])

[VBRP]: // Billing Document: Item Data
Load

[VBELN] as [Billing Document_VBELN.VBRK].....

SQL Select VBELN from VBRP
;
STORE * FROM [VBRP] INTO F:\Whirlpool_QlikView\Working\sudhanshu\VBRK VBRP\VBRP2013.QVD(qvd);
DROP TABLE [VBRP];

thanks

Re: need to left join sql table in qlikview

Gysbert_Wassenaar — Mon, 22 Dec 2014 12:30:54 GMT

Left join([VBRK])

[VBRP]: // Billing Document: Item Data
Load [VBELN] as [Billing Document_VBELN.VBRK].....

The left join will merge the data with the [VBRK] table. There won't be a table [VBRP] created because of the join. And since you're only loading Billing Document_VBELN.VBRK which already exists in [VBRK] you will not see any change in [VBRK].

Re: need to left join sql table in qlikview

Mon, 22 Dec 2014 12:33:30 GMT

its not performing left join.

its loading whole vbrp table.

Re: need to left join sql table in qlikview

Gysbert_Wassenaar — Mon, 22 Dec 2014 12:38:20 GMT

That does not seem possible to me, based on what you posted.

It can only load the field [Billing Document_VBELN.VBRK] since you only load the field VBELN:

SQL Select VBELN from VBRP

Since you're using a left join the table [VBRP] will not be created and the field [Billing Document_VBELN.VBRK] will be joined (i.e. merged) with [VBRK]. In other words [VBRK] does not change and [VBRP] is not created.

Re: need to left join sql table in qlikview

Mon, 22 Dec 2014 15:12:14 GMT

Hi Gybert,

and thanks for your reply.

just becoz it was taking too much space I removed other fields.it was taking all vbrp fields.

I tried something else and that worked fine.

I really don't understand ,why it did not worked with left join.

I used where sub query.with that it works perfectly.

but I cant thank enough for your support.

thanks.

S Shrivas