SQL code conversion to Qlikview for use with QVDs **HELP!**

[MalcolmCICWF]

Hi, I am new to Qlikview and still fresh to SQL/Oracle. I have been teaching myself as I go with success. I am currently converting some of our business's SQL written QV reports to run off new Qvds I have created.I have done some searching in the manual with no luck. I have taken a crack at my version of these to no avail, could someone help me so I have futureexamples of these?

CASE WHEN FORWARDER_ID IS NULL THEN 'N/A' ELSE FORWARDER_ID END AS FORWARDER_ID

SUBSTR(VINTAGE_CODE,8,1) AS PROG

MONTHS_BETWEEN(FILE_DATE, ACCT_CHG_OFF_DATE) AS MONTH_DIFF

[#2 · Accepted solution]

Hi,

As I understood, you'd like to use the SQL syntax in Qlikview script for example when loading data.

If that's so, you can use the following:

LOAD

if(IsNull(Field1), 'N/A', Field1) as Field1,

Mid(Field2, 8, 1) as Field2,

Interval(Date(DateField2, 'DD/MM/YYYY') - Date(DateField1, 'DD/MM/YYYY'), 'D') as NumberOfDays

From TestFile.qvd(qvd);

Regarding the interval function, you can divide it by 30, 31 or 30.43685 (365.242199/12), and use Round or Floor to get an integer.

You may also want to use the fabs() function to get the absolute value in case Date2 is smaller than Date1 and you are using the Floor() function.

You'd have for example:

Round(Interval(Date(DateField2, 'DD/MM/YYYY') - Date(DateField1, 'DD/MM/YYYY'), 'D')/30) as NumberOfMonths.

However, if you can still read from your sql database to create your qvds, i'd suggest you keep using your sql syntax as below:

LOAD FORWARDER_ID, PROG, MONTH_DIFF;

SQL SELECT

CASE WHEN FORWARDER_ID IS NULL THEN 'N/A' ELSE FORWARDER_ID END AS FORWARDER_ID,

SUBSTR(VINTAGE_CODE,8,1) AS PROG,

MONTHS_BETWEEN(FILE_DATE, ACCT_CHG_OFF_DATE) AS MONTH_DIFF

FROM ...;

This will ensure you get the same result as SQL especially regarding the number of months between two dates.

Hope this helps.

[MalcolmCICWF]

Thank you, everthying worked just fine and now I have examples to look back on in the future!