Data being brought in as 0 or -1

[diwaskarki]

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.

[aarkay29]

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]

I tried that, It didn't bring anything.

[aarkay29]

Could you please share the app with some sample data

[vishsaggi]

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]

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]

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 · 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,

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]

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

Thanks,
V.