8 Replies Latest reply: Jan 21, 2016 6:44 AM by Chetan Kumar Gopinath RSS

    Qlikview macro to compare headers and data in files(csv,xls,xlsx)

      Hi All,

       

      In order to provide validation dashboard let me know a Qlikview macro to compare headers of latest file vs previous file.

       

      Regards,

      Chetan

        • Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)
          Hirish V

          Hi ,

           

          May be like this,

          Here i have compared two excels in  which there is change in one header its denoted at the front end, do check this

          code:

          for each vExt in 'csv', 'xlsx', 'xls'
              for each vFile in Filelist('YourPath\YourfilePrefix*.' & '$(vExt)')
                  let vLen@1 = len('$(vExt)');
                  let vLen@2 = len(subfield('$(vFile)', '.', -1));
                  if $(vLen@1) - $(vLen@2) = 0 then
                      let vFileFormat = pick(match('$(vExt)', 'csv', 'xlsx', 'xls'),
                          '(txt, codepage is 1252, no labels, delimiter is '';'')',
                          '(ooxml, no labels, table is Sheet1)',
                          '(biff, no labels, table is @1)');
                      let vConcat = if(noofrows('MetaData') > 0, 'concatenate (MetaData)', 'MetaData:');
                      $(vConcat)
                      First 1
                      Load filebasename() as FileName, FileExtension() as FileTyp, * From $(vFile) $(vFileFormat);
                  end if
              next
          next
            
          MetaDataCrosstable:
          crosstable(Field, FieldName, 2) Load * Resident MetaData;
          drop table MetaData;
          
          exit script;
          
          

           

          Output:

          Comparision of excel header.PNG

           

          Hope this helps,

          PFA,

          Regards,

          Hirish