I need help writing an else statement

[diwaskarki]

I am new to qlikview. How do I write a script for this:

if(status is complete and "completed on" doesn't exist)
use string 4
else
use "Completed On"

I would really appreciate the help. Thanks

[ianwilson]

Hi Diwas

Example below, your statement needs the line:

IF(Status='Complete' AND ISNULL(Date)=0,String,Date) AS Date2

Full example:

Test1:
Load * inline
[
Status,Date,String
Complete,01/01/17,A
Complete,02/01/17,B
Complete, ,C
Incomplete,01/01/17,D
Incomplete,02/01/17,E
Incomplete,,F
];

Test2:
Load Status,
Date,
String,
IF(Status='Complete' AND ISNULL(Date)=0,String,Date) AS Date2
Resident Test1;

Drop table Test1;

[MarcoWedel]

please post some sample data and your expected result.

thanks

regards

Marco

[diwaskarki]

Hello Ian

Thanks for responding. Why do I need the "Date" in AND ISNULL(Date)=0,String,Date) .

Shouldn't ISNULL(Date)=0,String) AS Date2 do it?

[diwaskarki]

Hello Marco,

Thanks for your reply.

I have a field name 'ATTRIBUTES' in the database table named spt_audit_event, that holds an xml. It looks like this.

<Attributes>

  <Map>

    <entry key="Business Case" value="test"/>

    <entry key="Identities" value="TD1J"/>

    <entry key="Line of Business" value="Auto"/>

    <entry key="Offices" value="28 - North Atlantic"/>

    <entry key="Request Type" value="Add"/>

    <entry key="Requested By" value="Swapnil Agrawal (D03F)"/>

    <entry key="Requested On" value="Wed, Feb 24 06:42:15 CST 2016"/>

    <entry key="Template" value="BL - Farm USA - PA2 - DE"/>

    <entry key="Template Group" value="Business Line Auto"/>

    <entry key="Work Area" value="Business Lines"/>

    <entry key="Completed on" value= "Wed, Feb 25 06:42:15 CST 2016"/>

  </Map>

</Attributes>

SPT_AUDIT_EVENT has other field Im using . They are INSTANCE, STATUS and STRING4.

However, in our database before sometime in 2016, "Attributes" did not store "Completed on" field. It was stored in STRING4. So I need to come up with this condition.

IF(INSTANE = 'Auto Fire Role Access Request' AND STATUS='Complete' , DO A TEXTBETWEEN IN THE ATTRIBUTES TO BRING IN 'Completed On', if its there

if 'completed on ' is not there in ATTRIBUTES use string4 as  'Completed On'.

[MarcoWedel]

maybe something like:

SPT_AUDIT_EVENT:
LOAD RecNo() as ID,
    INSTANCE,
    STATUS,
    STRING4,
    ATTRIBUTES,
    If(WildMatch(ATTRIBUTES,'*Completed on*'),TextBetween(ATTRIBUTES,'"Completed on" value= "','"/>'),STRING4) as [Completed on]
Inline [
INSTANCE STATUS STRING4 ATTRIBUTES
Instance1 Status1 String4_1 <Attributes><Map><entry key="Business Case" value="test"/><entry key="Identities" value="TD1J"/><entry key="Line of Business" value="Auto"/><entry key="Offices" value="28 - North Atlantic"/><entry key="Request Type" value="Add"/><entry key="Requested By" value="Swapnil Agrawal (D03F)"/><entry key="Requested On" value="Wed, Feb 24 06:42:15 CST 2016"/><entry key="Template" value="BL - Farm USA - PA2 - DE"/><entry key="Template Group" value="Business Line Auto"/><entry key="Work Area" value="Business Lines"/><entry key="Completed on" value= "Wed, Feb 25 06:42:15 CST 2016"/></Map></Attributes>
Instance2 Status2 "Tue, Feb 24 05:31:04 CST 2016" <Attributes><Map><entry key="Test attribute" value="some text"/></Map></Attributes>
] (delimiter is '\t');

hope this helps

regards

Marco

[diwaskarki]

Hello Marco,

This is what I ended up using.

If(INSTANCE='Auto Fire Role Access Request', STRING3='Complete' AND len(STRING4)<14,TextBetween(ATTRIBUTES, '<entry key="Completed On" value="','"')),
If(INSTANCE='Auto Fire Role Access Request', STRING3='Complete' AND len(STRING4)>14, STRING4) As Complete_Date,

Script ran successfully. However, string4 is bringing either 0 or -1 .

[MarcoWedel]

Hi,

I guess you mean Complete_Date only has 0 and -1 as values.

The reason is that you tried to use comma as logical AND operator.

So instead of

INSTANCE='Auto Fire Role Access Request', STRING3='Complete' AND len(STRING4)>14

you could try with

INSTANCE='Auto Fire Role Access Request' AND STRING3='Complete' AND len(STRING4)>14

Your complete expression however might work as

If(INSTANCE='Auto Fire Role Access Request' AND STRING3='Complete',If(Len(STRING4)<14,TextBetween(ATTRIBUTES, '<entry key="Completed On" value="','"'), STRING4)) As Complete_Date

hope this helps

regards

Marco

[diwaskarki · Accepted solution]

If(INSTANCE='Auto Fire Role Access Request' AND STRING3='Complete' AND len(STRING4)<18,TextBetween(ATTRIBUTES, '<entry key="Completed On" value="', '"'),
If(INSTANCE='Auto Fire Role Access Request'AND STRING3='Complete' AND len(STRING4)>18, STRING4)) As Completed_Date,

Looks like it was bringing 0 and -1 and because the length was at least 17 at all times.

[MarcoWedel]

Nice, that means it's working for you now?

Then please go ahead and close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco