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. 

[Clement15]

Hello,

Here is a code that answers your question

[nickmarlborough]

Hi, 

it may be worth noting that i am using a from 

i.e.

LOAD

ID, 
Number Code, 

From File


Using what you have sent.... I have assumed it will look like this:
[Table1]:
Load

* (other fields),

ID,

left([Number Code],1) as FirstChar

from file

Load

ID,

SubField([Number Code],';') as [Number Code]

Resident [Table1];

this seems to give me an error... the error i get is that in the second part where i use resident, the field 'Number Code' is not recognised 'not found'

(i have assumed that i am sub out your Inline for the use of the from as i am extracting from a file)

Sadly didnt work - essentially in the second part where i am us

[anat]

first you have to split the records by using subfield then use left function to take first number

[Table1]:
Load

* (other fields),

ID

from file

noconcatenate

table2:

load *,left([Number Code],1) as FirstChar;

Load

ID,

SubField([Number Code],';') as [Number Code]

Resident [Table1];

[Clement15]

Can't you replace “Resident TEST2” with “From File” in the code I provided?

[nickmarlborough]

this didnt work either 😞

[Table 1]:

LOAD
[ID],
Text([Codes]) as [Number Codes]

FROM File

NoConcatenate

[Table2]:
LOAD
[ID],
left([Codes],1) as firstchar;

load
[ID],
subfield([Codes],';') as [Delimit Number Codes]
resident [Table 1];

[Clement15]

Try with the change in blue

[Table 1]:

LOAD
[ID],
Text([Codes]) as [Number Codes]

FROM File

NoConcatenate

[Table2]:
LOAD
[ID],
left([Delimit Number Codes],1) as firstchar;

load
[ID],
subfield([Codes],';') as [Delimit Number Codes]
resident [Table 1];

[nickmarlborough]

Hi, 

what happens is as follows (as an example - made up characters)

ID --------------------------- Number Codes -------------Number Codes -------------------FirstChar

ID1----------------------- James;Nick ---------------- James------------------------J

ID1------------------------James;Nick-----------------Nick--------------------------J

ID1------------------------James;Nick-----------------James--------------------------N

etc....

in other words the left and the newly created firstchar is not lining up to the correct value. instead its doing the left but not getting tied back to what it was left of

[nickmarlborough]

how can i btw create essentially x2 new columns - so that one is the delimited values (James and Nick) and then the second column is the J and the N

I apologise for switching the examples! thanks again

[Clement15]

So you want to have 4 columns in total? an ID, a concatenated code, the code alone and the first digit?