Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
diwaskarki
Creator II
Creator II

Data being brought in as 0 or -1

Hi,

I am doing a length comparison, and trying to bring different fields based on different conditions.

"String4" sometimes holds a date: "Wed,Feb 17 10:35:45 CST 2016" and sometimes a request ID: "DEZ21476208303201".

Im trying to bring the date held in "ATTRIBUTES", which is an xml, when string4 > 14 and when string4<14, I want to bring string4 as Completed_Date.

This is what my script looks like:

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,

This is what "ATTRIBUTES" field look like:

<Attributes>

  <Map>

    <entry key="Business Case" value="test group created for some qlikview testing work"/>

    <entry key="Create New Group" value="DK_B2E_QLIKVIEWTEST"/>

    <entry key="Platform/Environment" value="Existing / LDAP - B2E Test"/>

    <entry key="Request Type" value="Create Single Group"/>

    <entry key="Requested By" value="Diwas Karki (E1R1)"/>

    <entry key="Requested On" value="Wed, Nov 1 17:06:58 CDT 2017"/>

  </Map>

</Attributes>

Complete_Date is bringing in 0 or -1. Data should look like Wed,Feb 17 10:35:45 CST 2016. I would really appreciate any help.

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,

This is the correct script for what I was trying to do. Apparently an extra parenthesis in the TextBetween was making the first statement stop from being executed.

View solution in original post

8 Replies
aarkay29
Specialist
Specialist

try this

If(INSTANCE='Auto Fire Role Access Request',

   If(STRING3='Complete' AND len(STRING4)<14,TextBetween(ATTRIBUTES, '<entry key="Requested On" value="',chr(34))),

If(INSTANCE='Auto Fire Role Access Request',

if(STRING3='Complete' AND len(STRING4)>14, STRING4))) As Complete_Date,

diwaskarki
Creator II
Creator II
Author

I tried that, It didn't bring anything.

aarkay29
Specialist
Specialist

Could you please share the app with some sample data

vishsaggi
Champion III
Champion III

May be try this?

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

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

diwaskarki
Creator II
Creator II
Author

Vishwa,

I tried that and it got me the closest to what  I want.

Whats happening now is only the second line of IF statement is being executed.

Only this is being executed: If(INSTANCE='Auto Fire Role Access Request' AND STRING3='Complete' AND len(STRING4)> 14, STRING4))

vishsaggi
Champion III
Champion III

Try the other way around like:

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

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

Else can you share your sample file to look into?

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,

This is the correct script for what I was trying to do. Apparently an extra parenthesis in the TextBetween was making the first statement stop from being executed.

vishsaggi
Champion III
Champion III

If it resolved your issues can you close the thread marking your's as correct and any helpful responses accordingly.

Thanks,
V.