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