Getting Data from SAP tables (EKKO and EKPO)

[bimala0507]

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.

[#2]

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

[bimala0507]

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.

[#4]

PFA.

Thanks,

Ram

[#5]

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;

[#6]

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

[bimala0507]

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

[#8]

Use MaxValue in the list box.

Thanks,

Ram

[qlikpahadi07]

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

];