Load first 2 and last 2 columns from qvd

[ericdelaqua]

Hello,

we are generating some QVDs and I need to write a load script that rearranges the columns inside them and store into another QVD.

CURRENT QVD columns Example:

a,

b,

e,

f,

c,

d

I want to load and store this qvd dynamically (because the column names are always changing but the format is the same)

expected output QVD:

a,

b,

c,

d,

e,

f

can we load by column position (No)? any suggestion on how this can be setup dynamically?

Thanks

[pooja_prabhu_n]

Did you mean sorting the columns like below.

current_qvd:

LOAD * INLINE [

    Current

    a

    b

    e

    f

    c

    d

];

Output:

NoConcatenate

LOAD * Resident current_qvd

Order by Current;

DROP Table current_qvd;

Store Output into Output.qvd(qvd);

[petter · Accepted solution]

I happen to have some load scripts to do more or less what you want:

This one reads all QVDs in a certain folder and rearrange to order of the fields in alphabetical order:

filename=;tablename=;fieldno=;fieldname=;sortedfieldlist=;newfilename=;
QVD_location = 'E:\DATA\QVD\';
FOR Each filename in FileList( QVD_location & '*.QVD' )
  IF Right(Upper(filename),5) <> '_.QVD' THEN
    LOAD * FROM "$(filename)" (QVD);
    tablename = TableName(0);
    FOR fieldno=1 TO NoOfFields(tablename)
      fieldname = FieldName(fieldno,tablename);
      FIELDS: LOAD * INLINE [$(fieldname)] (no labels);
    NEXT
    [SORTEDFIELDS]: LOAD Concat('['&@1&']',',',@1) AS sortedfieldlist RESIDENT [FIELDS]; 
    sortedfieldlist = Peek('sortedfieldlist');
    newfilename = Left(filename,Len(filename)-4) & '_.QVD';
    STORE $(sortedfieldlist) FROM [$(tablename)] INTO "$(newfilename)" (QVD);
    DROP TABLES [FIELDS],[SORTEDFIELDS],[$(tablename)];
  END IF
NEXT
QVD_location=;filename=;tablename=;fieldno=;fieldname=;sortedfieldlist=;newfilename=;  // clean up

This one has been modified to read all QVDs and rearrange in alphabetical order and just keep the first two and the last two:

filename=;tablename=;fieldno=;fieldname=;sortedfieldlist=;newfilename=;
QVD_location = 'E:\DATA\QVD\';
FOR Each filename in FileList( QVD_location & '*.QVD' )
  IF Right(Upper(filename),5) <> '_.QVD' AND Right(Upper(filename),5) <> '!.QVD'  THEN
    LOAD * FROM "$(filename)" (QVD);
    tablename = TableName(0);
    FOR fieldno=1 TO NoOfFields(tablename)
      IF fieldno<=2 OR fieldno>NoOfFields(tablename)-2 THEN
        fieldname = FieldName(fieldno,tablename);
        FIELDS: LOAD * INLINE [$(fieldname)] (no labels);
      END IF
    NEXT
    [SORTEDFIELDS]: LOAD Concat('['&@1&']',',',@1) AS sortedfieldlist RESIDENT [FIELDS]; 
    sortedfieldlist = Peek('sortedfieldlist');
    newfilename = Left(filename,Len(filename)-4) & '!.QVD';
    STORE $(sortedfieldlist) FROM [$(tablename)] INTO "$(newfilename)" (QVD);
    DROP TABLES [FIELDS],[SORTEDFIELDS],[$(tablename)];
  END IF
NEXT
QVD_location=;filename=;tablename=;fieldno=;fieldname=;sortedfieldlist=;newfilename=;  // clean up

The last script could be modified to not rearrange/sort but only keep first two and last two:

     Change line 13 in the last script above into this:

     [SORTEDFIELDS]: LOAD * AS sortedfieldlist RESIDENT [FIELDS];

[petter]

It is also possible to read the field information from the QVD-meta part first and then do a load with sorted and/or limited number of fields after that. That will save you from having to rewrite every QVD-file... I don't have a script for that yet but if you need give me a hint.

[ericdelaqua]

Hi Petter,

Thank you for sharing this routine. That is exactly what I needed for my scenario. Really appreciate.