Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In Qlikview you can use the subfield() function to subdivide a field based on a delimiter. I have a field in the database which contains a customer and supplier number. These need to be stored as each time 2 records, but with an indication of the type. Is it possible to test on the iteration when using the subfield function (without using a for next loop and inter record functions ?
Example :
Database field custsup (1 field delimited by a ','):
A, B
C, D
This needs to be transformed into a table containing 2 fields :
A CUS
B SUP
C CUS
D SUP
The first subfield iteration needs to have CUS in the second field, the 2nd SUP, etc...
Is this possible without creating a loop ?
I was thinking of something like : if(subfieldcount(custsup) = 1 then 'CUS' else 'SUP' end) or something like that. Unfortunately, the subfieldcount function does not exist.
You could make to separate loads. The first on reads the field until comma as CUS, the second reads erverything after the comma.
- Ralf
Subfield without any parameters works great in this situations.
See the example here http://community.qlik.com/cfs-filesystemfile.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.40.74/subfield.qvw.
Another way is to mark the data after the comma and make a stacked load:
LOAD if(left(marked_field, 1)='#', mid(marked_field, 1) marked_field) as value
, if(left(marked_field, 1)='#', 'SUP', 'CUS') as type;
LOAD subfield(replace(field, ', ', ',#'), ',') as marked_field from ...;
Hi all,
Ralf's answer is right, here you are the possible solution.
Best regards
Daniel, this could be very dangerous if there is one field without a comma 😉
I know, but it's a very interesting use of the subfield function and you only do 1 load.