Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
diwaskarki
Creator II
Creator II

I need help writing an else statement

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

1 Solution

Accepted Solutions
diwaskarki
Creator II
Creator II
Author

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.

View solution in original post

9 Replies
ianwilson
Contributor III
Contributor III

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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:

QlikCommunity_Thread_281313_Pic1.JPG

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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