topic How to split this string in QlikView

How to split this string

Wed, 26 Sep 2012 15:02:56 GMT

Hi all,

I want to split string.

String is "2012-09-23 03:11:28: 0860 store Tbl into ..\..\qvd\derived\invoice\INVOICE_ITEM_SUPPLIER.qvd"

I need Like below

Time=2012-09-23 03:11:28: 0860

Table=store Tbl into ..\..\qvd\derived\invoice\INVOICE_ITEM_SUPPLIER.qvd

Thanks in Advance

Regards,

Kabilan K.

Re: How to split this string

whiteline — Wed, 26 Sep 2012 15:25:17 GMT

Hi.

=trim(left(String, index(String, 'store')-1))

=right(String, len(String)-index(String, 'store')+1)

Re: How to split this string

Thu, 27 Sep 2012 14:42:55 GMT

Hi whiteline,

Here, I have issue.

Issue is:

I want to store this value "trim(left(String, index(String, 'store')-1))" as Date format with time, because I need to take a time from that.

Ex:

I used to write a script like this: But StartTime returns nothing

Load Time(trim(left(String, index(String, 'store')-1))) as StartTime

From SomeTable;

Can Any on help me.

Thanks in advance.

Regards,

Kabilan K

Re: How to split this string

whiteline — Thu, 27 Sep 2012 17:29:04 GMT

Hi.

Why do you use time() finction ?

The time function formats the expression as time according to the string given as format-code.

So it converts the text representation of numerical time value (you could use it later).

To convert string datetime to numeric value you should use timestamp#() or date#() function with corresponding format string:

TimeStamp#(trim(left(String, index(String, 'store')-1)), 'YYYY-MM-DD hh:mm:ss: ffff') as StartTime

Re: How to split this string

Fri, 28 Sep 2012 06:13:38 GMT

Hi Whiteline,

Thank You For your all reply.

Could u tel me one thing?

How to calculate the difference between two times?

EX:

StartTime =12:27:42

EndTime =12:28:38

Diference=Timestamp#((EndTime-StartTime),'MM:SS') // It shows some nemeric value like 0.00066....

But I want to show as Difference=0.56 Mins

Thanks in advance.

Regards,

Kabilan K.

Re: How to split this string

whiteline — Fri, 28 Sep 2012 07:14:45 GMT

Hi.

There are two functions Timestamp#() and Timestamp().

The first one converts string value to numeric value.

The second one changes the time format.

Since you've already converted your StartTime and EndTime, why do you use Timestamp# instead of Timestamp ?

Re: How to split this string

Fri, 28 Sep 2012 10:43:26 GMT

Hi Whiteline,

Thanks For Everything.

And, I have a on issue.

I have data like this:

I have got date from this data.

Script is Date(String) // String = Image data

In that image the dates are same. so, In result, I need to get only one row as 2012-09-23 ryt?

But I got the date list box like this:

Please help me..

Thanks In advance..

Regards,

Kabilan K.

Re: How to split this string

miikkaqlick — Fri, 28 Sep 2012 11:06:57 GMT

Hi!

In that image all dates are different. Those are date-times, in first row time is 47 minutes, 3 seconds and 426 milliseconds past midnight. In Qlikview all dates have numerical format and those values are in numerical format something like this:

40624.0047030426

40624.0047030427

40624.0047030429

For example Floor() -function will reduce all those date-times to same date-time.:

40624.000000000

Miikka

Re: How to split this string

Gysbert_Wassenaar — Fri, 28 Sep 2012 11:07:51 GMT

try this: date#(subfield(MyString),' ',1) as MyDate

Re: How to split this string

Fri, 28 Sep 2012 11:22:07 GMT

Hi All,

Thanks...

@whiteline: Thanks a lot.

@Miikka: Superp.. Explanation is good.

@Gwassenaar: Your ans is also useful

Regards,

Kabilan K

Re: How to split this string

Fri, 28 Sep 2012 11:37:15 GMT

Hi All,

Issue:

I have pivot chart like this:

But I need to make a Chart Like this:

Any possible is here?, please let me know.

Thanks In advance.

Regards,

Kabilan K.

Re: How to split this string

whiteline — Fri, 28 Sep 2012 12:37:19 GMT

Toggle chechbox Properties->Presentation->Show partial sums.