Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
psenthilbtech
Contributor III
Contributor III

Loading from QVD

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

6 Replies
petter
Partner - Champion III
Partner - Champion III

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

psenthilbtech
Contributor III
Contributor III
Author

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

psenthilbtech
Contributor III
Contributor III
Author

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

petter
Partner - Champion III
Partner - Champion III

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.

petter
Partner - Champion III
Partner - Champion III

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; 

psenthilbtech
Contributor III
Contributor III
Author

Hi Thanks Petter for your help