How to load my data the right way

[lucas3]

Hi all,

I am loading a bunch of data from an excel file into qliksense

I have a column named "Account Name" and another one named "Account Numbers Strings" which contains data of this form: "A|B|C|D|E|F|G"
where A, B, C, etc are valuable informations (actually accounts numbers).

In one cell, I have no fixed ammount of information, I can have just "A", "A|B|C" or "A|...|Z|AA|..|AZ|BA|........" with no fixed limit.

I am wondering how to load my data in such a way that I have only 2 fields: "Account Name" and "Account Number", where "Account Number" can contain only one Account Number

Is that feasible in QlikSense without touching my Excel file ?

Thank you

[marcus_sommer · Accepted solution]

It could be done with subfield(), like:

load AccountName, subfield(AccountString, '|') as AccountNumber
from Excel;

- Marcus

[lucas3]

It works thanks !
Other question, how to load a constant number field ?

I tried with Load 1 as "FieldName" but it does not work

[marcus_sommer]

The following counter-fields might be useful in your case:

load
   recno() as RecNo, rowno() as RowNo, iterno() as IterNo,
   AccountName, subfield(AccountString & '|', '|', iterno()) as AccountNumber
from Excel
while iterno() <= substringcount(AccountString, '|') + 1;

Subfield() is now specified with 3 parameters what disabled the loop-functionality of it and this feature is added per while-loop which enables the possibility to use iterno() as a counter.

- Marcus