Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
please post some sample data and your expected result.
thanks
regards
Marco
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?
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'.
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
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 .
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
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.
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