2 Replies Latest reply: Jun 1, 2011 7:26 PM by Erich Shiino RSS

    Cross Table

    Anand Chouhan

      Hi All,

       

      If i loading multiple *.xls cross tables and if any one file has no data on that means, suppose i load 10 *.xls Cross tables and one of them is empty.So how can i get that information that a perticular file is empty.

       

       

      Anand

      Regards

        • Re: Cross Table
          Angus Monro

          Hi Anand,

           

          what you can do is include an extra column as you load your data, and populate that column with the name of the data file that the record is from.  This then enables you to determine how many records were loaded from each file.  It would look something like this:

           

           

          MyData:
          CrossTable(yourparameters) 
          LOAD 
               FileBasename() AS DataSource,
               *
          FROM *.xls (biff, table is Your_worksheet_name$, other parameters); 
          
          
          RecordCounts:
          LOAD
               DataSource,
               COUNT(*)
          resident MyData;
          


          Hope this helps,

           

          Angus.

          • Cross Table
            Erich Shiino

            Hi,

            You could use this piece of code to list all the files on a folder, according to the same string you are using to load the files:

             

            for each File in filelist ('C:\Files\*.xls')

             

            load '$(File)' as File

             

            autogenerate(1) ;

             

            next

             

            Then, on the load of the XlS you can use FileName() on the load of the file, to get the file name as an extra field.

             

            something like:

            crosstable (etc... )

            load

            filename() as File, *

            from ...

             

            Then, on the interface, you can create a chart with FILE as dimension and count(someFieldHere) to check if you get some zeros (you may need to uncheck the suppress zeros on the presentation tab)

             

            If this sounds confusing, please upload some sample files and scripts and I can write something more similar to your case.

             

            Hope this helps,

             

            Erich