Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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);
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];
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.
Hi Petter,
Thank you for sharing this routine. That is exactly what I needed for my scenario. Really appreciate.