topic AW:Re: AW:Formatting date fields coming from SAP-BWP in QlikView

Formatting date fields coming from SAP-BWP

googel84 — Wed, 28 Jul 2010 20:08:58 GMT

Goodmorning, this is my first post in QlikView community forum so, as you can think, I'm pretty new to QlikView scripting language.

The problem I'm facing concerns formatting of date fields as they come from SAP-BW in a better way then we do at the moment.

Two extracts of the code I'd like to receive suggestion about follows.

Default settings

</h4>
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='h.mm.ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h.mm.ss[.fff]';
SET MonthNames='Gen;Feb;Mar;Apr;Mag;Giu;Lug;Ago;Set;Ott;Nov;Dic';
SET DayNames='Lun;Mar;Mer;Gio;Ven;Sab;Dom';

Here I set the default variables according to my PC regional settings.

Date management


[Campagna di vendita (temp)]:
SQL SELECT
 "SALESDEAL",
 "COMP_CODE",
 "DISTR_CHAN",
 "SALESORG",
 "DATEFROM",
 "DATETO"
 FROM SAPBWP."/BI0/PSALESDEAL"
 WHERE
 "OBJVERS" = 'A';
INNER JOIN SELECT
 "SALESDEAL",
 "TXTLG"
 FROM SAPBWP."/BI0/TSALESDEAL";

[Campagna di vendita]:
LOAD
 [SALESDEAL] AS [Campagna di vendita],
 [COMP_CODE] AS [Società],
 [DISTR_CHAN] AS [Canale di distribuzione],
 [SALESORG] AS [Organizzazione commerciale],
 makedate(left("DATEFROM",4),mid("DATEFROM",5,2),right("DATEFROM",2)) AS [Inizio validità],
 makedate(left("DATETO",4),mid("DATETO",5,2),right("DATETO",2)) AS [Fine validità],
 [TXTLG] AS "Campagna di vendita (descr.)"
RESIDENT [Campagna di vendita (temp)];

STORE [Campagna di vendita] INTO VEN_BW_IOB_Campagna di vendita.qvd;
DROP TABLE [Campagna di vendita (temp)];
DROP TABLE [Campagna di vendita];

The objective is to load the records we need from a table and formatting date field as a date and not as a number. These fields come as strings with the following format: YYYYMMDD (no separation characters).

As you can see, I loaded data twice: the first time from BW using a SELECT statement but without setting aliases and without formatting any date field (because the language interpreter did not let me do it) and then from the internal table Campagna di vendita (temp) with a LOAD statement. This turn, I was able to set aliases and formatting date fields by means of makedate function.

At the end I store the resulting table into a qvd file and drop the tables.

Here is my first question: is there a better way to reach the same result? It's clear that it's a waste of resource, even if I'm able to see date fields correctly formatted when using a List Box to show them.

Now I have an other doubt. The fact is that when I try to build a Table Box using date fields as dimensions, it seems the fields looses their format and are seen as number again and there's no way to change this behavior manually from Table Box property.

What am I mistaking?

Thank you in advance.

Alberto

AW:Formatting date fields coming from SAP-BWP

Wed, 28 Jul 2010 21:06:10 GMT

Hi Alberto,

I'd try to do the LOAD-statement before the SQL - thus you wouldn't have to load the data a second time (resident). In the LOAD-statement, you might use the 'date#()'-function as follows:

LOAD...

date#(DATEFROM, 'YYYYMMDD') as DateFrom,

...

SQL...

Hope, this helps.

Regards

Helmut

AW:Formatting date fields coming from SAP-BWP

googel84 — Thu, 29 Jul 2010 08:41:59 GMT

Hi Helmut, I'm sorry but your suggestion is not applicable because the LOAD statement loads data from a file and doesn't let us charging from a DB2 table (DB2 is the DBMS under our SAP-BW).

I tried it anyway and the error I get is the following:

Formatting date fields coming from SAP-BWP

suniljain — Thu, 29 Jul 2010 14:08:49 GMT

date(makedate(left(Trim(SAP_DATE),4),mid(Trim(SAP_DATE),5,2),right(Trim(SAP_DATE),2))) as Converted_DATE

Pls Try This one. It is tested logic in SAP BW

AW:Re: AW:Formatting date fields coming from SAP-BWP

Thu, 29 Jul 2010 14:18:21 GMT

Alberto,

you then might want to swap again (LOAD after SQL). But the syntax stays the same, try

LOAD...

date#("DATEFROM", 'YYYYMMDD') as DateFrom,

...

regards

Helmut

Formatting date fields coming from SAP-BWP

googel84 — Thu, 29 Jul 2010 15:12:00 GMT

I tried it but I don't see any difference as compared to makedate(left("DATEFROM",4),mid("DATEFROM",5,2),right("DATEFROM",2)).

However, now I think the second and less important part of my post is answered. Probably the strange behavior described was caused by a wrong Table Box configuration. To be more precise, now I see the same values both in the List Box and in the Table Box.

The important case is the one described above, at the beginning of the post.

Thanks

AW:Re: AW:Formatting date fields coming from SAP-BWP

googel84 — Thu, 29 Jul 2010 15:19:02 GMT

Hi Helmut, the date# function returns the number associated to the date passed, and it's not what I'm looking for; but I used it to reach the same result as with makedate in a shorter way:

date(date#("DATEFROM",'YYYYMMDD')

Thanks

AW:Re: AW:Formatting date fields coming from SAP-BWP

googel84 — Thu, 05 Aug 2010 14:47:36 GMT

Goodmorning, any new suggestion?

Alberto

AW:Re: AW:Re: AW:Formatting date fields coming from SAP-BWP

Thu, 05 Aug 2010 14:55:17 GMT

Hi Alberto,

could you please provide a (small) qvw to show the difference between what you get and what you want it be?

Thank you.

Regards

Helmut

AW:Re: AW:Re: AW:Formatting date fields coming from SAP-BWP

googel84 — Wed, 11 Aug 2010 13:36:22 GMT

Hi Helmut, I'm sorry for the dealy.

Here is the qvw you requested.

As you can see, I do 2 loading cycles (SELECT and then LOAD statements) to have DATEFROM and DATETO correctly formatted into, respectively, Inizio validità and Fine validità. So, these last two fields are the result we'd like to reach without, if possible, doing the second cycle.

I also tried to apply date format directly into the SELECT statement, looking at DB2 online documentation. The function I used in another script is the following:

DATE(SUBSTR(DOC_DATE, 7, 2)||'.'||SUBSTR(DOC_DATE, 5, 2)||'.'||SUBSTR(DOC_DATE,1, 4)) AS "DOC_DATE_CONV"

The problem with this function is that the date a get is not in the right format: MM/DD/YYYY instead of DD/MM/YYYY.

Anyway, the more I become familiar to QlikView, the more I think that the only way to avoid a second cycle is to find the way to use "standard" SQL.

Do you agree with me?

AW:Re: AW:Re: AW:Formatting date fields coming from SAP-BWP

googel84 — Wed, 11 Aug 2010 13:37:45 GMT

Hi Helmut, I'm sorry for the dealy.

Here is the qvw you requested.

As you can see, I do 2 loading cycles (SELECT and then LOAD statements) to have DATEFROM and DATETO correctly formatted into, respectively, Inizio validità and Fine validità. These last two fields are the result we'd like to reach without, if possible, doing the second cycle.

I also tried to apply date format directly into the SELECT statement, looking at DB2 online documentation. The function I used in another script is the following:

DATE(SUBSTR(DOC_DATE, 7, 2)||'.'||SUBSTR(DOC_DATE, 5, 2)||'.'||SUBSTR(DOC_DATE,1, 4)) AS "DOC_DATE_CONV"

The problem with this function is that the date a get is not in the right format: MM/DD/YYYY instead of DD/MM/YYYY.

Anyway, the more I become familiar to QlikView, the more I think that the only way to avoid a second cycle is to find the way to use "standard" SQL.

Do you agree with me?

AW:Re: AW:Re: AW:Re: AW:Formatting date fields coming from SAP-BWP

Tue, 17 Aug 2010 17:26:53 GMT

Hi Alberto,

sorry for the delay, as well.

What happens if you try to use the expressions

makedate

(left("DATEFROM",4),mid("DATEFROM",5,2),right("DATEFROM",2)) AS [Inizio validità],

makedate

(left("DATETO",4),mid("DATETO",5,2),right("DATETO",2)) AS [Fine validità],

in your SQL-statement? Do you get any errors loading or only in formatting the datefields right?

Regards

Helmut

AW:Re: AW:Re: AW:Re: AW:Formatting date fields coming from SAP-BWP

googel84 — Wed, 18 Aug 2010 09:36:14 GMT

Hi Helmut, I tried what you suggest even if I knew it would have probably brought me to an error because we did it at the beginning.

The fact is that using SELECT statement means using "standard" SQL.

Thanks.

AW:Re: AW:Re: AW:Re: AW:Re: AW:Formatting date fields coming from SAP-BWP

Wed, 18 Aug 2010 09:58:46 GMT

Hi Alberto,

I'd try to use LOAD before SELECT like this:

[Campagna di vendita]:

LOAD

[COMP_CODE]

AS [Società],

[DISTR_CHAN]

AS [Canale di distribuzione],

[SALESORG]

AS [Organizzazione commerciale],

makedate

(left("DATEFROM",4),mid("DATEFROM",5,2),right("DATEFROM",2)) AS [Inizio validità],

makedate

(left("DATETO",4),mid("DATETO",5,2),right("DATETO",2)) AS [Fine validità],

[TXTLG]

AS "Campagna di vendita (descr.)";

SQL

SELECT

"SALESDEAL",

"COMP_CODE",

"DISTR_CHAN",

"SALESORG",

"DATEFROM",

"DATETO"

FROM

= 'A';

INNER

JOIN SELECT

"SALESDEAL",

"TXTLG"

FROM

SAPBWP."/BI0/TSALESDEAL"; SAPBWP."/BI0/PSALESDEAL"

WHERE"OBJVERS"

[SALESDEAL],

Sorry I can't test it myself...

I've appended the test-qvw with that option. What Happens if you do it this way?

Regards

Helmut

AW:Re: AW:Re: AW:Re: AW:Re: AW:Formatting date fields coming from SAP-BWP

googel84 — Wed, 18 Aug 2010 13:58:43 GMT

Hi Helmut, first of all thanks for contributing to this post. It's very userful when the discussion leads to knowledge share.

I tried your code and actually it seems that it doesnt do a second turn loading the disired data but I think that, although not evidently, the system does it because run time remains the same. So I can just say I can put in a different way the code but without any performance gain.

Alberto

P.S.Please follow the instructions suggested in this post in order to correctly add code to a posthttp://community.qlik.com/forums/p/15789/61380.aspx

AW:Re: AW:Re: AW:Re: AW:Re: AW:Re: AW:Formatting date fields coming from SAP-BWP

Wed, 18 Aug 2010 14:14:28 GMT

Hi Alberto,

run time remains the same because the system has to do the same things but the data is not put into an internal table at first rather beeing evaluated "in one sweep". But you don't have to use the temporary table - let this do QV for you.

Regards

Helmut

SV:Formatting date fields coming from SAP-BWP

Thu, 19 Aug 2010 13:05:26 GMT

Why don't just use:

date

(date#(DATEFROM,'YYYYMMDD'),'DD/MM/YYYY')




[Campagna di vendita (temp)]:
SQL SELECT
 "SALESDEAL",
 "COMP_CODE",
 "DISTR_CHAN",
 "SALESORG",
 "DATEFROM",
 "DATETO"
 FROM SAPBWP."/BI0/PSALESDEAL"
 WHERE
 "OBJVERS" = 'A';
INNER JOIN SELECT
 "SALESDEAL",
 "TXTLG"
 FROM SAPBWP."/BI0/TSALESDEAL";

[Campagna di vendita]:
LOAD
 [SALESDEAL] AS [Campagna di vendita],
 [COMP_CODE] AS [Società],
 [DISTR_CHAN] AS [Canale di distribuzione],
 [SALESORG] AS [Organizzazione commerciale],
 date(date#(DATEFROM,'YYYYMMDD'),'DD/MM/YYYY') as [Inizio validità],,
 //makedate(left("DATEFROM",4),mid("DATEFROM",5,2),right("DATEFROM",2)) AS [Inizio validità],
 makedate(left("DATETO",4),mid("DATETO",5,2),right("DATETO",2)) AS [Fine validità],
 [TXTLG] AS "Campagna di vendita (descr.)"
RESIDENT [Campagna di vendita (temp)];

STORE [Campagna di vendita] INTO VEN_BW_IOB_Campagna di vendita.qvd;
DROP TABLE [Campagna di vendita (temp)];
DROP TABLE [Campagna di vendita];

SV:Formatting date fields coming from SAP-BWP

googel84 — Thu, 19 Aug 2010 14:42:14 GMT

Thanks Max, that's for sure a way to correctly format the the date but it doesn't avoid a second cycle to be done.

My goal it's not only to find another way to correctly format a date field, it's rather to avoid to charge data twice, explicitly or implicitly. You do it explicitly when running the original posted code as it is; you do it implicitly if you run the code as Helmut suggests to modify it.

I think the only way to reach the goal is to use the right DB2 instruction, maybe finding the way to extract the date as a number.

I'm still trying and I'll let you know all.

Alberto