Date conversion SAP Field LIKP.WADAT_IST doesn't work

[hlauerhaas]

Hi everyone,

it drives me mad. I have tables from SAP (LIKP delivery head and LIPS delivery position) where a date WADAT_IST can be found. If I want to extract the year, month and so on at the designer, it works pretty well. When doing the same at the script, QlikView gives back the strangest values. Here is the relevant part of my code and a screenshot of the tablechart:

Lieferung:
LOAD	VBELN 																								AS LIKP_LIPS.VBELN_Lieferung, 
		POSNR 																								AS LIKP_LIPS.POSNR_Lieferpostion,
		VBELN & '-' & POSNR																					AS LIKP_LIPS.VBELN_POSNR,
		VGBEL 																								AS LIKP_LIPS.VGBEL_Vorgaengerbeleg,
		VGPOS 																								AS LIKP_LIPS.VGPOS_Vorgaengerbelegposition,
		VGBEL & '-' & VGPOS 																				AS LIKP_LIPS.VGBEL_VGPOS, //verknüpftes Feld
		LFIMG																								AS LIKP_LIPS.LFIMG_gelieferteMenge;		
SQL 	SELECT	VBELN POSNR LFIMG VGBEL VGPOS
		FROM	LIPS
		WHERE	VGBEL IS NOT NULL AND VGBEL > 0 AND VGPOS > 0;

LEFT JOIN (Lieferung)
LOAD	VBELN																								AS LIKP_LIPS.VBELN_Lieferung,
		WADAT_IST																							AS LIKP_LIPS.WADAT_IST_IstWarenbewegungsdatum,
		date(date#(WADAT_IST,'YYYYMMDD'),'YYYY/MM/DD')														AS LIKP_LIPS.WADAT_IST_IstWarenbewegungsdatum_Date#,
		MakeDate(Left(WADAT_IST,4), Mid(WADAT_IST, 5,2), Right(WADAT_IST,2)) 								AS LIKP_LIPS.WADAT_IST_IstWarenbewegungsdatum_MakeDate,
		date(WADAT_IST)																						aS LIKP_LIPS.WADAT_IST_IstWarenbewegungsdatum_Datum,
				Year(WADAT_IST) 																			As LIKP_LIPS.WADAT_IST_Jahr,
				ApplyMap('Mapping_Quartal', month(WADAT_IST), Null()) 										As LIKP_LIPS.WADAT_IST_Quartal,		
				Month(WADAT_IST) 																			As LIKP_LIPS.WADAT_IST_Monat,
				Date(monthstart(WADAT_IST), 'YYYY-MM') 														As LIKP_LIPS.WADAT_IST_JahrMonat,	
				Week(WADAT_IST) 																			As LIKP_LIPS.WADAT_IST_Woche,  
				WeekYear(WADAT_IST) & '-' &  num(Week(weekstart(WADAT_IST)),'00')  							As LIKP_LIPS.WADAT_IST_Kalenderwoche,  
				Day(WADAT_IST) 																				As LIKP_LIPS.WADAT_IST_Tag,  
				WeekDay(WADAT_IST) 																			As LIKP_LIPS.WADAT_IST_Wochentag,
				Year(WADAT_IST) + Evaluate(ApplyMap('Mapping_Geschaeftsjahr', month(WADAT_IST), Null())) 	AS LIKP_LIPS.WADAT_IST_Geschaeftsjahr, 
				ApplyMap('Mapping_Geschaeftsperiode', month(WADAT_IST), Null()) 							AS LIKP_LIPS.WADAT_IST_Geschaeftsperiode,
				Year(WADAT_IST) + Evaluate(ApplyMap('Mapping_Geschaeftsjahr', month(WADAT_IST), Null())) 	 
					& '-' & ApplyMap('Mapping_Geschaeftsperiode', month(WADAT_IST), Null()) 				AS LIKP_LIPS.WADAT_IST_Geschaeftsjahrperiode,
		ROUTE																								AS LIKP_LIPS.ROUTE_Transportroute,
		ApplyMap('MAPPING_ROUTE',ROUTE,'#NV') 																AS LIKP_LIPS.ROUTE_Transportroute_Tage;		
SQL		SELECT 	VBELN WADAT_IST ROUTE 
		FROM 	LIKP;

I don't have any idea, what i am doing wrong. It has already taken me days. Please help.

kind regards

Heike

[marcus_sommer]

Personally I would quite probably not do it in this way else I would just join both tables without any formatting of the date or creating further period-fields - and then using a master-calendar (and maybe a financial calendar) which are associated to your target-table. For more information see here: How-to-use-Master-Calendar-and-Date-Values.

This isn't a direct solution to your failed formatting-stuff - date(date#()) with the proper format-strings should work well and if there are multiple different formattings an alt(Date1, Date2, ...) will catch them all - but it should simplify your task.

- Marcus

[hlauerhaas]

Hello Marcus,

thank you for replying. To use a master- or a canonical calendar does not work, because I would get circular references. I already tryed to integrate such a calendar. Besides it does not solve the actual problem, because I also could not get a regular date out of my field.

Anyway, thanks

Heike

[marcus_sommer]

Hi Heike,

the handling of multiple calendars within an application could be difficult but with a proper naming of the fields it's possible to avoid synthetic keys and circular loops.

Beside this if your converting of the date/timestamp-fields failed it will be caused from a not suitable format-string - maybe there are any additionally spaces or other special chars included or your date is really a timestamp or ... This means you need to know exactly the data to apply the right converting.

If you load the SAP data from a flat-file export you could use a editor like Notepad ++ to look which data are really there and by loading them with a SAP connector or a ODBC driver (without an applied converting/formatting) you could use QlikView to see how the data look like and also measure them with len(), ord() and so on.

- Marcus

[hlauerhaas]

Hi Marcus,

it is frustrating. Exporting from SAP to plain text gives the format YYYY-MM-DD. But using this format with date# also doesn't work. I now tried all formulas (in the script) which could give a hint:

As you can see I got bull**bleep**. Only the last two records are correct. But I identyfied that if I only read these 8 VBELN_POSNR each date is converted correctly. May there be a bug, handling quite a lot data at the SAP Connector?

Heike.

[marcus_sommer]

Hi Heike,

yes, it looks strange. I don't know any of the SAP connectors (our main sources are different and the few monthly SAP data which we include are manually exported) and therefore I have no own experience of how it's technically implemented. But if I look on your screenshot it seems that the date is a dual-value and that there is something wrong with the string-representation.

Therefore I'm not sure that your suggestion that the last two records are the right ones are correct. In regard to the results of various ord-values and the date# and num I would assume that num contains the right values and would use:

date(YourNumExpression) // probably just num(Field)

to get the right date-value. Of course you need to check this with any unique ID and/or an additionally recno() / rowno() within the loading.

- Marcus

[hlauerhaas]

Hi Marcus,

got it. It seems to be the QlikView-Client. When running the script a number of times seriatim, I got different results every time. Dates converting wrong work fine at the next load and dates looking fine did not work at the next load. Other users  have the same problem. Running the same script with the Qlikview AccessPoint have given always the right dates. 

So I think I got the cause but not the reason why. 

I have 11.20.12904.0 SR12 64-bit installed on Win10. Other users, who testet it for me, have 64-bit Win7 and 32-bit Win7. I think I will address it to the support of our distributor for clarifying wether there is a bug known.

Nethertheless many thanks for your efforts.

Heike.