Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

split a field into different fields

I have a field named 'sources'. For examples 1A_florida, 1B_florida, 1C_florida, 1A_georgia, 1B_georgia, 1C_georgia

If I want to create a new field '1A', '1B', '1C'. What is the best way to do this?

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

If that is the case, then you need to use the Left() function instead, and keep only the first two characters:

Left('1Bflorida', 2) // returns 1B

Left('1B_florida', 2) // also returns 1B

Miguel

View solution in original post

10 Replies
its_anandrjs

Best way is use Subfield function see the example for that

LOAD sources,SubField(sources,'_',1) as Newsources;

LOAD * Inline

[

sources

1A_florida

1B_florida

1C_florida

1A_georgia

1B_georgia

1C_georgia

];

Not applicable

what does the 1 mean in (sources, '_', 1) ?

Miguel_Angel_Baeyens

Subfield() extracts

  • from the string specified in the first parameter,
  • using the separator specified in the second parameter, all values (if there is no third parameter)
  • or the position specified in third parameter regarding the separator.

In your case, the first position. If you specify 2 instead you will get "florida" and "georgia".

Miguel

Not applicable

I have some fields that also that are 1Bflorida and 1Bgeorgia without the _ delimiter.

If I wanted to create a new field with all '1B' values, what third parameter would I use to show me both 1Bflorida and 1B_florida

Miguel_Angel_Baeyens

If that is the case, then you need to use the Left() function instead, and keep only the first two characters:

Left('1Bflorida', 2) // returns 1B

Left('1B_florida', 2) // also returns 1B

Miguel

View solution in original post

Not applicable

That won't work because then I will have to go through each single 'source' (there are over 100 sources) and do the left() function

Not applicable

Miguel's solution works:

LOAD sources,left(sources,2) as Newsources;
LOAD * Inline
[
sources
1A_florida
1B_florida
1C_florida
1A_georgia
1B_georgia
1C_georgia
];

its_anandrjs

In the expression (sources, '_', 1)  1 is parameter means from the first position in the source field extract the string. As Miguel explain if your requirement is that in the string there is no symbol _ then use left function and if you know you have to pick only first two word then use left function.

MarcoWedel

Does this mean that the source could be longer than 2 characters?

Can you post some more examples to clarify the range of possible values?

Thanks

Regards

Marco