topic Some String Manipulation in QlikView

Some String Manipulation

QFanatic — Fri, 11 Feb 2022 13:10:08 GMT

hello all,

I am reading a value that looks something like this....

1/31/2022 0:0:0 AM SAST

I need to deduct the date from that field, subtract it from today, and get the Days difference.

So what I'm currently doing:

1. left(ATTR_VALUE,(index(ATTR_VALUE,' ')) - 1) ...I look for the first space and read everything to the left of it. This returns '1/31/2022'. It is NOT in date format yet so I do the following:

2. date(date#(left(ATTR_VALUE,(index(ATTR_VALUE,' ')) - 1),'mm/dd/yyyy'),'YYYY/MM/DD')....this returns '2022/01/31' which is correct

3. Then I need to get the Days difference between Today() and the date in point (2)...

INTERVAL(TODAY() - date(date#(left(ATTR_VALUE,(index(ATTR_VALUE,' ')) - 1),'mm/dd/yyyy'),'YYYY/MM/DD'),'D') as DaysDiffernce - which returns 10 days (as at today)

That is a LOT of coding to get a result....is there a quicker way of achieving the same result?

Thank you

Re: Some String Manipulation

hic — Fri, 11 Feb 2022 14:00:25 GMT

One alternative is the following:

1) Use Subfield() in the script to extract the date:
SubField(ATTR_VALUE,' ',1)

2) Interpret this string - also in the script:
Date#(SubField(ATTR_VALUE,' ',1),'M/D/YYYY') as MyDate

2) No need to format intermediate results. You get the right answer simply by using:
Today() - MyDate as Difference

Re: Some String Manipulation

marcus_sommer — Fri, 11 Feb 2022 14:16:24 GMT

It depends. If you find a valid format-pattern for your timestamp the following might be enough:

interval(today() - timestamp#(FIELD, 'Format'), 'D')

I'm not sure if SAST could be fetched with the format-pattern. If not you need any additional measure like your cutting-method or maybe a removing with replace() or keepchar() or purgechar(). The next point is could there be real time-parts - if yes you may need a floor() to remove it (depends if you just show 'D' with interval() or if you want to calculate further with the result).

In each case you could remove the date() within your logic. Another approach to keep the script or expression more readable would be to place the logic within a (parametrized) variable.

- Marcus

Re: Some String Manipulation

QFanatic — Fri, 11 Feb 2022 15:03:52 GMT

I'm honored - two Guru's answering my question.😀

Thank you so much!