topic Re: Split Field in Field Name and Values in QlikView

Split Field in Field Name and Values

pamaxeed — Tue, 27 Oct 2015 09:28:21 GMT

Hi guys,

is there a way to split the following Dimension Value:

Dim1:a;Dim2:x;

in the following output that i can have my table in qlikview like that:

Dim1	Dim2
a	x

Do I need to use Generic Load Prefix?

Thanks,

Patric

Re: Split Field in Field Name and Values

sunny_talwar — Tue, 27 Oct 2015 09:31:38 GMT

Generic prefix is one option. Or you can alos do this:

Table1:

LOAD Dim1

FROM yourTable;

Table2:

LOAD Dim2

FROM yourTable;

Re: Split Field in Field Name and Values

pamaxeed — Tue, 27 Oct 2015 09:37:34 GMT

Is that working?

For instance my field could be also like that:

Field1: Dim1:1,2;Dim2:1;

So we have more values for Dim1 for instance and it should be translated in the right table output like above:

FIELD1 DIM1 DIM2

Dim1:1,2;Dim2:1; 1 1

Dim1:1,2;Dim2:1; 2 1

Re: Split Field in Field Name and Values

sunny_talwar — Tue, 27 Oct 2015 09:40:34 GMT

It should. Are you looking to do this in the script or front end of the application?

Re: Split Field in Field Name and Values

miguelbraga — Tue, 27 Oct 2015 09:41:29 GMT

Hi Patric,

You can use this:

For Dim1:

=Left('Dim1:a;Dim2:x;', 4)

For Dim2:

=Mid('Dim1:a;Dim2:x;', 8, 4)

For expression to get "a" field:

=Mid('Dim1:a;Dim2:x;', 6, 1)

For expression to get "x" field:

=Mid('Dim1:a;Dim2:x;', 13, 1)

Regards,

Re: Split Field in Field Name and Values

pamaxeed — Tue, 27 Oct 2015 09:43:47 GMT

In the script my Input is like that:

TableInput:

Field1

Dim1:a;Dim2:x;;

Input:

Load Field1

From Dummy;

Output:

Load .......

Drop table Input;

where the output table looks like this:

FIELD1 DIM1 DIM2

Dim1:1,2;Dim2:1; 1 1

Dim1:1,2;Dim2:1; 2 1

Cheers,

Patric

Re: Split Field in Field Name and Values

pamaxeed — Tue, 27 Oct 2015 09:45:38 GMT

The problem is that the proposed solution is not flexible:

I have to have a generic solution, this means that my input key field can look also like that:

Field1

Asterix:1,2,3;Position:a,b,x;

Patric

Re: Split Field in Field Name and Values

miguelbraga — Tue, 27 Oct 2015 09:45:56 GMT

Like this:

Re: Split Field in Field Name and Values

pamaxeed — Tue, 27 Oct 2015 09:49:09 GMT

Sry that is not working for me.

I have to have a generic solution.

Re: Split Field in Field Name and Values

miguelbraga — Tue, 27 Oct 2015 09:51:02 GMT

I'll take a look if there is a way to solve your problem

Re: Split Field in Field Name and Values

sunny_talwar — Tue, 27 Oct 2015 09:53:08 GMT

May be this:

TableInput:

LOAD SubField(SubField(SubField(Field1, ';', 1), ':', 2), ',') as DIM1,

SubField(SubField(SubField(Field1, ';', 2), ':', 2), ',') as DIM2;

LOAD * Inline [

Field1

Dim1:1,2;Dim2:1

] (delimiter is |);

Output

Re: Split Field in Field Name and Values

pamaxeed — Tue, 27 Oct 2015 09:57:57 GMT

Sunny thanks a lot.

It is what I was looking for!

At least it brings me a step furhter!

Patric

Re: Split Field in Field Name and Values

miguelbraga — Tue, 27 Oct 2015 09:57:59 GMT

The first dimension is given like this:

=SubField('Dim1:a;Dim2:x;', ':')

The second dimension is given like this

=SubField(SubField('Dim1:a;Dim2:x;', ':', 2), ';', 2)

"x" like this:

=SubField(SubField('Dim1:a;Dim2:x;', ';', 2), ':', 2)

"a" like this:

=SubField(SubField('Dim1:a;Dim2:x;', ';'), ':', 2)

Re: Split Field in Field Name and Values

sunny_talwar — Tue, 27 Oct 2015 09:59:54 GMT

Awesome

I am glad I was able to help you move forward.

Best,

Sunny

Re: Split Field in Field Name and Values

miguelbraga — Tue, 27 Oct 2015 09:59:58 GMT

I've found a similar question in community, take a look:

Split String Function In QlikView