topic Loading from QVD in App Development

Loading from QVD

psenthilbtech — Thu, 09 Aug 2018 03:44:10 GMT

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

Re: Loading from QVD

petter — Thu, 09 Aug 2018 04:33:19 GMT

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

Re: Loading from QVD

psenthilbtech — Thu, 09 Aug 2018 08:39:44 GMT

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

Re: Loading from QVD

psenthilbtech — Thu, 09 Aug 2018 08:40:39 GMT

Can you share your email id. so that i can share the application file

Re: Loading from QVD

petter — Thu, 09 Aug 2018 08:53:45 GMT

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.

Re: Loading from QVD

petter — Thu, 09 Aug 2018 09:15:22 GMT

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;

Re: Loading from QVD

psenthilbtech — Mon, 13 Aug 2018 03:36:30 GMT

Hi Thanks Petter for your help