topic Re: Re: Load field names beginning with .... in QlikView

Load field names beginning with ....

Thu, 10 Jul 2014 14:52:47 GMT

Hi,

I'm looking to load fields from a table that begin with a string (ex) all fields beginning with TEST_

How would this look in the load script?

Thanks!

Re: Load field names beginning with ....

Or — Thu, 10 Jul 2014 14:56:17 GMT

I'm not sure if you could easily load just those fields, but if you're OK with loading the entire table and then dropping irrelevant fields, you should be able to use the technique detailed here .

Re: Load field names beginning with ....

rwunderlich — Thu, 10 Jul 2014 15:02:41 GMT

Is the table you are loading from an SQL Table?

-Rob

Re: Load field names beginning with ....

Thu, 10 Jul 2014 15:05:47 GMT

Hi Rob,

It will either be from a RESIDENT table or a qvd.

Re: Load field names beginning with ....

Thu, 10 Jul 2014 15:11:14 GMT

You can use WildMatch, here's a simple scenerio:

tmp:

load * Inline

[

fields,

test_one

test_two

three

four

test_five

sixe

test_seven

];

load if(WildMatch(fields, 'test_*'), fields) as myfield

Resident tmp;

Only fields with "test_" will load.

Re: Load field names beginning with ....

Thu, 10 Jul 2014 15:24:43 GMT

Hi Lior,

Thanks for the reply. The above script matches cells within the field whereas I'm looking to load all fields that begin with a certain string.

Here's an example to make it clearer:

LOAD TEST, TEST1, TEST2, TEST3, ID, SALES

RESIDENT JOHN;

What I would like to do in the above load statement is only load fields beginning with TEST (ie)
TEST, TEST1, TEST2, TEST3

Re: Load field names beginning with ....

Thu, 10 Jul 2014 15:49:25 GMT

Hi John,

Nope. The expression that Lior provided is correct.

load if(WildMatch(fields, 'test_*'), fields) as myfield

Resident tmp;

You will see that he uses * behind test_ which means that you will only load fields starting with test_. If you did the following '*test_*' it would actualy load everything that contains the word test_ inside it.

Hope this helps.

Regards,
Marius

Re: Re: Load field names beginning with ....

rwunderlich — Thu, 10 Jul 2014 16:43:56 GMT

Hi John,

Two approaches I can think of.

1. Load all fields and then drop the ones you don't want.

mytab:

LOAD * FROM ....;

FOR i = NoOfFields('mytab') to 1 STEP -1

LET vFieldName = FieldName($(i),'mytab');

IF NOT '$(vFieldName)' LIKE 'TEST*' THEN

DROP FIELD [$(vFieldName)];

ENDIF

NEXT i

Note that you have to work the fieldlist backwards because DROP will change the existing field indexes.

2. Build a list of fields to load from a resident table or QVD.

SET vList=;

SET vComma=;

FOR i = 1 to NoOfFields('mytab')

LET vFieldName = FieldName($(i),'mytab');

IF '$(vFieldName)' LIKE 'TEST*' THEN

LET vList = '$(vList)' & '$(vComma)' & '[$(vFieldName)]';

SET vComma=',';

ENDIF

NEXT i

tab2:

LOAD $(vList)

RESIDENT mytab

;

Note it's the same pattern for QVDs. Just sustitute in QVD functions QvdNoOfFields() and QvdFieldName().

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Load field names beginning with ....

Thu, 10 Jul 2014 16:58:30 GMT

Excellent stuff Rob, that's exactly what I was after!

Thanks Marius and Lior for your help also.

Re: Load field names beginning with ....

Thu, 10 Jul 2014 17:23:58 GMT

Very nice Rob! I started looking into similar option for dropping fields, but ran into the problem of field index changing after dropping fields because I was incrementing instead of decrementing...

Re: Re: Load field names beginning with ....

smarties — Thu, 19 Oct 2023 14:58:42 GMT

Thank Rob.

Been struggling with this problem for a while now, and the answer was to take away rather than add. Simple when you come at the problem from a different direction!