topic Re: Field values need to separate in QlikView

Field values need to separate

Sun, 22 Jan 2017 19:09:10 GMT

Hi All,

Could you please help me to separate the below field values .

Example data:

PA_1:1305.0

PA_2:1311.0

PA_3:1306.0

PA:0.0

recDate=}

recDate=2016-11-1519:19:22.010}

recDate=2016-11-1519:19:22.110}

recDate=2016-11-1519:19:22.210}

recDate=2016-11-1519:19:22.310}

Output should be as like below

1305.0
1311.0
1306.0
0.0
}
2016-11-1519:19:22.010
2016-11-1519:19:22.110
2016-11-1519:19:22.210
2016-11-1519:19:22.310

Re: Field values need to separate

MK_QSL — Sun, 22 Jan 2017 19:19:34 GMT

Data:

Load

YourFieldName as OriginalFieldName,

IF(SubStringCount(YourFieldName,'recDate='), Replace(SubField(YourFieldName,'recDate=',-1),'}',''), SubField(YourFieldName,':',-1)) as NewFieldName

Inline

[

YourFieldName

PA_1:1305.0

PA_2:1311.0

PA_3:1306.0

PA:0.0

recDate=}

recDate=2016-11-1519:19:22.010}

recDate=2016-11-1519:19:22.110}

recDate=2016-11-1519:19:22.210}

recDate=2016-11-1519:19:22.310}

];

Re: Field values need to separate

surendraj — Sun, 22 Jan 2017 19:29:21 GMT

Hi Venkatesh,

For first 4 fields

you can apply

SubField('PA_1:1305.0',':',2)

For 5th field

subfield('recDate=}','=',2)

For last 4 fields

trim(replace(subfield('recDate=2016-11-1519:19:22.010}','=',2),'}',' '))

Based on your requirement you can use the sub field function,to extract set of field value by using delimiter separater.

Hope this may help you!!

--Surendra

Re: Field values need to separate

Sun, 22 Jan 2017 19:32:31 GMT

Hi Manish,

Thanks for the replay ,

i got it but i have one more value including above mentioned which is like this

elapsedTime=27710

how to get the value like this "27710" ?

However the output values are coming like string in list box ?

Re: Field values need to separate

Sun, 22 Jan 2017 19:33:49 GMT

Hi Surendra,

Tanks for the reply but i should get all the values into same field .

Re: Field values need to separate

MK_QSL — Sun, 22 Jan 2017 19:38:12 GMT

Data:

Load

YourFieldName as OriginalFieldName,

IF(SubStringCount(YourFieldName,'='),

PurgeChar(SubField(YourFieldName,'=',-1),'}'),

SubField(YourFieldName,':',-1)) as YourNewFieldName2

Inline

[

YourFieldName

PA_1:1305.0

PA_2:1311.0

PA_3:1306.0

PA:0.0

elapsedTime=27710

recDate=}

recDate=2016-11-1519:19:22.010}

recDate=2016-11-1519:19:22.110}

recDate=2016-11-1519:19:22.210}

recDate=2016-11-1519:19:22.310}

];

Re: Field values need to separate

Sun, 22 Jan 2017 19:47:13 GMT

Hi Manish,

Thank you so much it is working perfectly however data was showing like text in list box (left side )

will it possible to get into right side ?

Re: Field values need to separate

surendraj — Sun, 22 Jan 2017 19:51:03 GMT

Properties-->Presentation-->alignment-->(enable right)..

Re: Field values need to separate

MK_QSL — Sun, 22 Jan 2017 19:59:16 GMT

Data:

Load

YourFieldName as OriginalFieldName,

IF(SubStringCount(YourFieldName,'recDate='),Replace(SubField(YourFieldName,'recDate=',-1),'}',''),SubField(YourFieldName,':',-1)) as NewFieldName,

IF(SubStringCount(YourFieldName,'='),

Alt(

TimeStamp#(PurgeChar(SubField(YourFieldName,'=',-1),'}'),'YYYY-MM-DDhh:mm:ss.fff'),

Num(PurgeChar(SubField(YourFieldName,'=',-1),'}'))

SubField(YourFieldName,':',-1)) as YourNewFieldName2

Inline

[

YourFieldName

PA_1:1305.0

PA_2:1311.0

PA_3:1306.0

PA:0.0

elapsedTime=27710

recDate=}

recDate=2016-11-1519:19:22.010}

recDate=2016-11-1519:19:22.110}

recDate=2016-11-1519:19:22.210}

recDate=2016-11-1519:19:22.310}

];