topic Max Date in QlikView

Max Date

Tue, 07 Jun 2011 12:03:22 GMT

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!

Max Date

Tue, 07 Jun 2011 12:45:53 GMT

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

Max Date

erichshiino — Tue, 07 Jun 2011 12:54:19 GMT

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;

[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

Max Date

Tue, 07 Jun 2011 13:12:36 GMT

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

Max Date

Tue, 07 Jun 2011 13:15:10 GMT

Hy Erich!

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

Max Date

erichshiino — Tue, 07 Jun 2011 13:20:51 GMT

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.

Max Date

Tue, 07 Jun 2011 13:39:18 GMT

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