Load field names beginning with ....

[#1]

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!

[Or]

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 .

[rwunderlich]

Is the table you are loading from an SQL Table?

-Rob

[#4]

Hi Rob,

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

[#5]

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.

[#6]

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

[#7]

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

[rwunderlich · Accepted solution]

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

[#9]

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

Thanks Marius and Lior for your help also.

[#10]

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