Subfield and Left

[nickmarlborough]

Hi

My Raw data looks like:

ID ------------------- Number Code

ID 1----------------- 1234;1245;8765

ID 2-------------------1234 ; 2848 ; 9008

I want to have it so Qlik takes this raw data and delimits the Number Code by row and then takes takes the first letter.

ID--------------- Number Code----------------- FirstChar

ID 1------------- 1234---------------------------1

ID 1 -------------1245 ---------------------------1

ID 1 --------------8765 ----------------------- 8

and so on......

how would i do this in the load editor? As i guess it is taking the left first letter from a new column (delimited by row) - any help would be appreciated. Ideally something i can paste in! been looking at this for ages and cant get to work. 

[nickmarlborough]

that is correct yes - the same there are a concat of 2 names - there should only be 2 rows for the ID if that makes sense. 

[marcus_sommer]

I suggest that you follow my recommendations from Re: Delimit by Row - Qlik Community - 2422185 and investigating only this table - deleting everything else - within a table-box. Subfield() and left() will be working and if you don't get the expected values they are applied wrongly and/or you are looking on wrong data in an inappropriate view. Only a table-box with unique id's like the recno/rowno are suitable to look on the real existing data.

[Clement15]

I hope that is what you want. English is not my first language, I may be missing something.

Test:
Load *,
Left( [Delimit Name] , 1) as Firstchar ;
Load
IDD,
[Name],
SubField([Name],';') as [Delimit Name]

FROM File ;

[stevedark]

Hi @nickmarlborough 

I think what you are missing here is that you need to do the subfield first and then subsequently do the left.

You can do this with a thing called a Preceding Load. These effectively use one chunk of code as a data source for another. You can build up as many layers as you need.

This code:

LOAD
   ID,
   [Number Code],
   Left([Number Code],1) as [First Char]
;
LOAD
   ID,
   SubField([Number Code], ';') as [Number Code]
INLINE
[
ID,Number Code
ID 1,1234;1245;8765
ID 2,1234;2848;9008
];

Gives this output:

Hopefully you can adapt this to your data.

This blog post explains the nuts and bolts of how the preceding load works, and some gotchas to be aware of with it:
https://www.quickintelligence.co.uk/preceding-load-qlikview/

Hope that helps.

Steve