topic Re: Re: Getting Data from SAP tables (EKKO and EKPO) in QlikView

Getting Data from SAP tables (EKKO and EKPO)

bimala0507 — Tue, 25 Mar 2014 05:08:07 GMT

Hi:

Anyone work on building the report by pulling the data from the above mentioned SAP tables. Currently I am loading it from Excel file. The problem I am facing in joining these 2 tables since there is one-to-many relationship. For each Material present in EKPO, I need to pick up the latest PO by comparing maximum PO_date.

EKPO (Purchase document Item)

Eg. MAT1 PO1

MAT1 PO2

MAT1 PO3

EKKO (Purchase Document Header)

PO1 1/1/2014

PO2 3/1/2014

PO3 5/1/2014

So ideally PO3 record should be picked up

anyone has worked on this kind of issue earlier. Appreciate a quick reply.

Re: Getting Data from SAP tables (EKKO and EKPO)

Tue, 25 Mar 2014 05:15:39 GMT

Use LastValue() function to get latest of the multiple records.

Re: Getting Data from SAP tables (EKKO and EKPO)

bimala0507 — Tue, 25 Mar 2014 05:25:17 GMT

Hi Neha:

Thanks much. Do you have the code for this. I am new to qlikview scripting. As per my knowledge, I should use group by clause.

I am storing both the files in qvd and reading it and trying to join both.

Re: Getting Data from SAP tables (EKKO and EKPO)

Tue, 25 Mar 2014 05:31:01 GMT

PFA.

Thanks,

Ram

Re: Getting Data from SAP tables (EKKO and EKPO)

Tue, 25 Mar 2014 05:31:54 GMT

Right now, I don't have code this tables. But below code might help you,

EKPO:

Load Material, PO_number

From EKPO;

join(EKPO)

Load PO_number, PO_date

From EKKO;

Purchase_Orders:

Load Material, LastValue(PO_Date) as PO_Date

Resident EKPO

Group by Material;

Drop Table EKPO;

Re: Re: Getting Data from SAP tables (EKKO and EKPO)

Tue, 25 Mar 2014 05:35:36 GMT

to extend it at the script level, it is like this.

Re: Getting Data from SAP tables (EKKO and EKPO)

bimala0507 — Tue, 25 Mar 2014 06:56:07 GMT

Hi Ramkumar:

Thanks for the quick help. But unfortunately, I have the personal edition, so will not be able to open the file. Is it possible the send the script in a notepad.

Thanks & Regards,

Bimala

Re: Getting Data from SAP tables (EKKO and EKPO)

Tue, 25 Mar 2014 07:34:18 GMT

Use MaxValue in the list box.

Thanks,

Ram

Re: Getting Data from SAP tables (EKKO and EKPO)

qlikpahadi07 — Tue, 25 Mar 2014 08:04:44 GMT

Hi Bimla,

here you can copy the example for script :

EKPO:

Load Mat, LastValue(PO) as PO Group By Mat;

load * inline

[

Mat,PO

MAT1,PO1

MAT1,PO2

MAT1,PO3

];

left join (EKPO)

LOAD * INLINE [

    PO, Date

    PO1, 1/1/2014

    PO2, 3/1/2014

    PO3, 5/1/2014

];