Skip to main content

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
lucas3
Contributor III
Contributor III

How to load my data the right way

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

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

It could be done with subfield(), like:

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

- Marcus

View solution in original post

3 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

It could be done with subfield(), like:

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

- Marcus

lucas3
Contributor III
Contributor III
Author

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
MVP & Luminary
MVP & Luminary

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