6 Replies Latest reply: Aug 12, 2018 11:36 PM by SENTHIL K RSS

    Loading from QVD

    SENTHIL K

      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 Skjolden

          This should work for you:

           

          T:
          LOAD 
          *
          FROM
          [C:\Users\pts\Downloads\Sample.qvd]
          (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 ...

          • Re: Loading from QVD
            Petter Skjolden

            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  
            [C:\Users\pts\Downloads\Sample.qvd]  
            (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;