Subfieldcount function

[#1]

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.

[rbecher]

You could make to separate loads. The first on reads the field until comma as CUS, the second reads erverything after the comma.

- Ralf

[danielrozental]

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.

[rbecher]

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 ...;

[#5]

Hi all,

Ralf's answer is right, here you are the possible solution.

Best regards

[rbecher]

Daniel, this could be very dangerous if there is one field without a comma 😉

[danielrozental]

I know, but it's a very interesting use of the subfield function and you only do 1 load.