topic AW:Subfieldcount function in QlikView

Subfieldcount function

Thu, 03 Dec 2009 21:24:25 GMT

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.

AW:Subfieldcount function

rbecher — Thu, 03 Dec 2009 21:35:39 GMT

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

- Ralf

Subfieldcount function

danielrozental — Thu, 03 Dec 2009 21:37:12 GMT

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.

AW:Subfieldcount function

rbecher — Thu, 03 Dec 2009 21:43:37 GMT

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

Subfieldcount function

Thu, 03 Dec 2009 21:44:11 GMT

Hi all,

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

Best regards

AW:Re: Subfieldcount function

rbecher — Thu, 03 Dec 2009 21:47:12 GMT

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

AW:Re: Subfieldcount function

danielrozental — Thu, 03 Dec 2009 21:49:31 GMT

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