need to left join sql table in qlikview

[#1]

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

[Gysbert_Wassenaar]

VBRK:

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

left join(VBRK)

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

[anbu1984]

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

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

[#4]

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

[#5]

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

[Gysbert_Wassenaar]

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].

[#7]

its not performing left join.

its loading whole vbrp table.

[Gysbert_Wassenaar]

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.

[#9]

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