Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team ,
I have a query .. Please find the attached QVD.
In the QVD, the header is I , J , K , L and so on.
But my requirement is when i load the QVD i should have the second line ie
DATA , AREA_CODE, OPERATOR ... and other field as header. How can we achieve this.
Regards,
Senthil
This should work for you:
T:
LOAD
*
FROM
(qvd);
f=;
FOR f#=1 TO NoOfFields('T')
fn = Peek(FieldName(f#,'T'),0);
IF IsNum(Num#(fn)) THEN
fn = Date(fn);
ENDIF
f = f & '[' & FieldName(f#,'T') & '] TO [' & fn & ']' & If(f#<NoOfFields('T'),',' & Chr(10));
NEXT
f#=;
fn=;
😧
NOCONCATENATE LOAD
*
RESIDENT
T
WHERE
RecNo()>1;
DROP TABLE T;
RENAME FIELDS $(f);
f=;
T1:
CROSSTABLE(DATE1,V1,5) LOAD
*
RESIDENT
D;
DROP TABLE D;
T: // This is necessary as the DATE1 was interpreted as string and not as a date due
// to an anomaly with CROSSTABLE
LOAD
*,
Date#(DATE1) AS DATE,
V1 AS V
RESIDENT
T1;
DROP FIELDS DATE1,V1;
DROP TABLE T1;
The lines 31 to 48 could be dropped if you don't need to unpivot the date columns ...
Hi Petter,
Thanks for your help. It works fine.
But how do we handle it for each and every month seperately.
The file which i attached was for January - So it had values till AT.
But for the April and few other months - it will be till AS.
Am attaching my file which i used for your reference. IT would be great if you can guide me accordingly
Regards,
Senthil
Can you share your email id. so that i can share the application file
This load script will handle it dynamically. Study it and you will see that it reads all the columns of the QVD however many there are and then dynamically rename all of them.
It should work fine with any number of months.
Here is a slightly updated version of the above load script that has more sensible table names and some more comments to make it a bit more understandable:
QVD_TABLE:
LOAD
*
FROM
(qvd);
f=; // Variable for the list of fields and the new field names
FOR f#=1 TO NoOfFields('QVD_TABLE') // find all the existing field names and the new ones
fn = Peek(FieldName(f#,'QVD_TABLE'),0); // find the new field name for field number f#
IF IsNum(Num#(fn)) THEN // Is the fieldname a serial date that is a number?
fn = Date(fn); // If so make it into a proper date
ENDIF
// append this fieldname into a string variable f to be used later in the script
f = f & '[' & FieldName(f#,'QVD_TABLE') & '] TO [' & fn & ']' & If(f#<NoOfFields('QVD_TABLE'),',' & Chr(10));
NEXT
f#=; // Remove the variables we don't need anymore
fn=;
// Now get only the pure data rows excluding the first record/row with field names
QVD_DATA:
NOCONCATENATE LOAD
*
RESIDENT
QVD_TABLE
WHERE
RecNo()>1; // Ignore the first record/row as it contains field names
DROP TABLE QVD_TABLE;
RENAME FIELDS $(f); // Use the f variable that contains the list of old and new fieldnames
f=;
THE_CROSSTABLE: // Need to do unpivot with the CROSSTABLE prefix to get month columns into rows
CROSSTABLE(DATE1,V1,5) LOAD
*
RESIDENT
QVD_DATA;
DROP TABLE QVD_DATA;
// This extra LOAD is necessary as the DATE1 was interpreted as a string and not as a date due
// to an anomaly with CROSSTABLE
DATA_TABLE:
LOAD
*,
Date#(DATE1) AS DATE,
V1 AS V
RESIDENT
THE_CROSSTABLE;
DROP FIELDS DATE1,V1; // Not necessary anymore
DROP TABLE THE_CROSSTABLE;
Hi Thanks Petter for your help