Need to know how to implement OR logic in Qlikview

[#1]

Hi All,

I have a below piece of code in PL/SQL I want to implement the same in Qlikview, Can anybody advice on the same. Please let me know if any more information is required:

Sample Code:

AND ( ( ( o177495.SDF_DATE BETWEEN TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1)
AND TO_DATE(:"To Date",UPPER('DD-MON-RRRR'),UPPER('NLS_DATE_LANGUAGE = AMERICAN'))
AND UPPER(o177495.ORDER_TYPE) LIKE UPPER('FCST%') )
OR
( TRUNC(o177495.SDF_DATE) BETWEEN TO_DATE(:"From Date",UPPER('DD-MON-RRRR'),UPPER('NLS_DATE_LANGUAGE = AMERICAN'))
AND TO_DATE(:"To Date",UPPER('DD-MON-RRRR'),UPPER('NLS_DATE_LANGUAGE = AMERICAN'))
AND UPPER(o177495.LN_ORD_STATUS) = UPPER('CLOSED') )
OR
( UPPER(o177495.ORDER_TYPE) IN (UPPER('REPLENISH ORD'),UPPER('STD ORD'),UPPER('REPLACEMENT ORD'),UPPER('EVAL/DEMO ORD'))
AND TRUNC(o177495.SDF_DATE) <= TO_DATE(:"To Date",UPPER('DD-MON-RRRR'),UPPER('NLS_DATE_LANGUAGE = AMERICAN'))
AND UPPER(o177495.LN_ORD_STATUS) <> UPPER('CLOSED') ) ) )
)

Regards,

kingshuk

[Miguel_Angel_Baeyens]

Hello Kingshuk,

OR works the same way it does in an SQL statement, and you're likely to pass that query right to your ODBC and your database. Anyway, if you want to replicate this query in a LOAD statement, the result may look like

LOAD *WHERE (SDF_DATE >= LAST_DAY <= "To Date" AND ORDER_TYPE LIKE "FCST*" ) OR (SDF_DATE >= "From Date" AND <= "To Date" AND UPPER(LN_ORD_STATUS) = 'CLOSED') OR (MATCH(UPPER(ORDER_TYPE), 'REPLENISH ORD', 'STD ORD', 'REPLACEMENT ORD', 'EVAL/DEMO ORD') > 0 AND SDF_DATE <= "To Date" AND UPPER(LN_ORD_STATUS) <> 'CLOSED');SQL SELECT *FROM Table;)

If your dates have different formats, you can use, say

Date('01-12-2010', 'DD-MM-YYYY')

function to compare them.

Hope that helps

[#3]

Hi Miguel,

Thanks for the reply, I had already explored this option, however the issue is as per the business requirement I need to take the date from the user in the report and then carryout the calculations.

Any advice on how I can do that.

Regards,

Kingshuk

[Miguel_Angel_Baeyens]

Hello Kingshuk,

Using the load statement will help you doing that. Say you need some date formatting, based on a "DateField" stored in the database, and create a new field for those Customers whose Country is DE or UK:

LOAD Date(DateField, 'DD-MM-YYYY') AS DateField, If(Match(Country, 'DE', 'UK') > 0, 1, 0) AS Customer_DE_UK;SQL SELECT DateFieldFROM Table;

Is better off this way than doing any formatting or transofrmation within the object.

Hope that helps.

[#5]

Hi,

Any pointers on executing this in the report by taking date inputs from the user at runtime.

Regards,

Kingshuk