How to use Convert and Case SQL functions in Qlik

[ashishpalkar]

Hi There

I want to use following SQL statement in Qlik, how can we use it script?

    CONVERT(VARCHAR,ISNULL(I.MaturityDate,I.expirydate),101) AS [MATURITY DATE],

     Rate= CASE WHEN I.instrumenttypetier1=2 THEN I.couponrate ELSE I.strikeprice END

Thanks in advance,

[mindaugas2048 · Accepted solution]

Try this way:

LOAD

DATE(IF(LEN([I.MaturityDate]) > 0, [I.MaturityDate], [I.expirydate]),'MM/DD/YYYY') AS [MATURITY DATE],

IF([I.instrumenttypetier1]=2, [I.couponrate], [I.strikeprice]) AS [RATE]

...

[ashishpalkar]

Thanks ,

Similarly can we use multiple ISNULL in Qlik?

ISNULL(I.instrumentcusip,ISNULL(I.instrumentsedol,ISNULL(I.instrumentisin,I.instrumentticker))) AS [SECURITY ID]

[agigliotti]

you can do as below:

Table:

LOAD *,

    if( I.instrumenttypetier1 = 2, I.couponrate, I.strikeprice ) as Rate;

sql select

....

CONVERT(VARCHAR,ISNULL(I.MaturityDate,I.expirydate),101) AS [MATURITY DATE],

I hope it helps.

[mindaugas2048]

You can but with IF() or ALT() statements, please check here for details:

https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/NULLFunct...

[mindaugas2048]

In your case, try the following script:

[test_data]:

LOAD * INLINE [

    I.instrumentcusip, I.instrumentsedol, I.instrumentisin, I.instrumentticker

    a, b, c, a

    1, , 3, 4

    ,,,

    ,1,2,

    2,,,

    3,,4

];

[result]:

LOAD *,

ALT(I.instrumentcusip,I.instrumentsedol,I.instrumentisin,I.instrumentticker) AS [SECURITY ID]

RESIDENT [test_data];

DROP TABLE [test_data];

[ashishpalkar]

Hi Andrea

any suggestions how to use below SQL statement in Qlik?

WHERE ISNULL(I.maturitydate,I.ExpiryDate) between @BusinessDate AND DATEADD(d,@DaystoMaturity,@BusinessDate)

[mindaugas2048]

Try this example,

here

[test_data] - your sql table data,

@DaystoMaturity = vBusinessDate, @BusinessDate =vDaystoMaturity - your variables:

LET vBusinessDate = FLOOR(MAKEDATE(2018,9,26));

SET vDaystoMaturity = 5;

[test_data]:

LOAD * INLINE [

    I.id, I.maturitydate, I.ExpiryDate

    1, 2018-01-01,

    2, , 2018-02-02

    3,2018-09-26, 2018-09-26

    4,,2018-09-28

    5,2018-09-27,

    6,2018-09-26,2018-09-27

    7,2018-09-27,2018-09-26

    8,,

    9,,2018-09-28

    10,2018-10-30,2018-09-30

];

[test result]:

LOAD

          [I.validation_id]

WHERE [I.validation_date] >= $(vBusinessDate) AND [I.validation_date] <=  $(vBusinessDate) + $(vDaystoMaturity);

LOAD

          [I.id]                                                                                          AS [I.validation_id],

          IF(LEN([I.maturitydate])>0, [I.maturitydate], [I.ExpiryDate])      AS [I.validation_date]

RESIDENT [test_data];

[ashishpalkar]

Thanks Mindaugas

I am getting some errors while executing your solution , please find my query  below which I am trying to execute where @DaystoMaturity is numeric number user will enter.

SELECT

AC.accountid   AS [ACCOUNT ID]

,AC.name  AS [ACCOUNT NAME]

,bu.businessunitname AS [BUSINESS UNIT]

,CONVERT(VARCHAR,ISNULL(I.MaturityDate,I.expirydate),101) AS [MATURITY DATE]

,ISNULL(I.instrumentcusip,ISNULL(I.instrumentsedol,ISNULL(I.instrumentisin,I.instrumentticker))) AS [SECURITY ID]

,I.instrumentid AS [INSTRUMENT ID]

,I.name AS [SECUIRTY NAME]

,Rate= CASE WHEN I.instrumenttypetier1=2 THEN I.couponrate ELSE I.strikeprice END

,POS.NetQuantity AS Quantity

,POS.marketvaluebook  AS [MARKET VALUE]

,POS.costbook AS [COST]

,POS.accruedinterestbook AS [ACCRUED INTEREST] 

,POS.PercentAssets AS [PERCENT_of_PORTFOLIO]

FROM instrumentequity I

INNER JOIN  position POS ON POS.instrumentid=I.instrumentid

AND POS.netquantity<>0

INNER JOIN account AC ON AC.accountid=POS.accountid

INNER JOIN businessunit BU ON AC.csbusinessunitid=BU.businessunitid

WHERE ISNULL(I.maturitydate,I.ExpiryDate) between @BusinessDate AND DATEADD(d,@DaystoMaturity,@BusinessDate)

AND POS.timeid=REPLACE(CONVERT(VARCHAR,@BusinessDate,102),'.','')

AND BU.BusinessUnitID IN (SELECT VAL FROM ftnReportingEnableMutliValue(@BusinessUnit,'|',1))

AND I.instrumenttypetier2 IN (SELECT VAL FROM ftnReportingEnableMutliValue(@AssetType,'|',1))

ORDER BY I.Maturitydate,AC.Accountid

[mindaugas2048]

If sql query returns the desired result, then just load the result as follows:

[result]:

LOAD

     [ACCOUNT ID],

     [ACCOUNT NAME],

...

     [PERCENT_of_PORTFOLIO];

SQL SELECT

     AC.accountid   AS [ACCOUNT ID],

     AC.name         AS [ACCOUNT NAME],

...

ORDER BY I.Maturitydate, AC.Accountid;

... or refine your task in more detail