Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

psenthilbtech
New Contributor

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

Re: Loading from QVD

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=;


D:

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
New Contributor

Re: Loading from QVD

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
New Contributor

Re: Loading from QVD

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

MVP
MVP

Re: Loading from QVD

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.

MVP
MVP

Re: Loading from QVD

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
New Contributor

Re: Loading from QVD

Hi Thanks Petter for your help

Community Browser