Max Date

[#1]

Hi Folks!

[Last Transaction]:
LOAD Only(%KEY_MAT_MATNR),
     BEL_MATNR_K,
     Max([Last Transaction])
Resident Documents Group By BEL_MATNR_K;

Wondering if someone can help me.

I create a table which contain about 8.000.000 lines.

If i want to show only the max data (last Transaction) for each Material (about 350.000) i used the script shown above.

My problem: i loose a lot of information . most of the materials are without a date, allthough there is a date in SAP. I only get about 60.000 materials with a date "Last Transaction". Every material has a transaction.

Can anyone tell me, where i've made tha mistake.

Thanks a lot for the help!

[#2]

Hi CT,

where is the Date in your Table? I am unable to understand your question. which tables are you using in SAP. I guess you are using MSEG..

can you explain a bit more. If you can tell the tables, I can help.

Regards

sravan

[erichshiino]

Why do you use the function only()?

I think it should be:

[Last Transaction]:

LOAD      BEL_MATNR_K,

     Max([Last Transaction])

Resident Documents Group By BEL_MATNR_K;

or

[Last Transaction]:

LOAD KEY_MAT_MATNR,

     BEL_MATNR_K,

     Max([Last Transaction])

Resident Documents Group By BEL_MATNR_K, KEY_MAT_MATNR;

You also have to make sure that QlikView can interpret your Last Transaction as a date field (if it is a string, the max() function will not work properly and in this case, the value would be ignored)

Hope this helps,

Erich

[#4]

Hy Sravan!

I use the tables MKPF and MSEG from SAP. With these two table i created the table "Documents" in QV by using a join. So far everything is fine.

Now i need the date (MKPF-BUDAT) of the last transaction for each material (MSEG-MATNR).

[#5]

Hy Erich!

I have replaced the only allready - my script looks like your suggestion - same result.

[erichshiino]

Did you check if Last Transaction is a date?

If it is a date, it's also a number, then you can use the Isnum( Last Transaction) to test it.

It will give you the wrong results, but only to check if it`s a date problem, you could replace the max(Last Trans.) by maxstring(Last Trans). It will consider everything as strings. If you have more results, then it`s a problem with dates.

[#7 · Accepted solution]

MSEG:

Load

Mid([Material doc], FindOneOf([Material doc], '123456789AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTyUuVvWwXxYyZz')) as [Material doc],

Mid(Material_Key, FindOneOf(Material_Key, '123456789AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTyUuVvWwXxYyZz')) as Material_Key,

;

SQL Select * from MSEG

where MJAHR>= 2011; // I guess you dont want all the data so reduce to 2011

l

left join (MSEG)

load

    Mid(MBLNR, FindOneOf(MBLNR, '123456789AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTyUuVvWwXxYyZz')) as [Material doc],

//    MJAHR as [Mat doc year],

    date(BUDAT) as [Posting date]

   ;

SQL Select MBLNR MJAHR BUDAT  from MKPF

where MJAHR >= 2011

;

Documents:

Noconcatenate

Load

[Material doc],

Material_Key

Max( [Posting date]) as [MaxPostingDate],

Resident MSEG

Group By [Material doc],Material_Key

;

Drop table MSEG;

Hi christoph,

This is what you need I guess and some advices from myside

The below formula is used to remove the zeroes before the MaterialNumber.

Mid([Material doc], FindOneOf([Material doc], '123456789AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTyUuVvWwXxYyZz')) as [Material doc]

Hope this helps

Regards

Sravan