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.
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.
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.
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.
[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];
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.
