Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

How to split this string

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.

1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: How to split this string

Hi.

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

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

11 Replies
whiteline
Honored Contributor II

Re: How to split this string

Hi.

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

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

Not applicable

Re: How to split this string

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

whiteline
Honored Contributor II

Re: How to split this string

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

Not applicable

Re: How to split this string

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),'MMSmiley FrustratedS') // It shows some nemeric value like 0.00066....

But I want to show as Difference=0.56 Mins

Thanks in advance.

Regards,

Kabilan K.

whiteline
Honored Contributor II

Re: How to split this string

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 ?

Not applicable

Re: How to split this string

Hi Whiteline,

Thanks For Everything.

And, I have a on issue.

I have data like this:

Capture.PNG

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:

Capture1.PNG

Please help me..

Thanks In advance..

Regards,

Kabilan K.

miikkaqlick
Contributor II

Re: How to split this string

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

40624.000000000

40624.000000000

BR

Miikka

Re: How to split this string

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


talk is cheap, supply exceeds demand
Not applicable

Re: How to split this string

Hi All,

Thanks...

@whiteline: Thanks a lot.

@Miikka: Superp.. Explanation is good.

@Gwassenaar: Your ans is also useful

Regards,

Kabilan K

Community Browser