topic Re: Cross Table in QlikView

Cross Table

its_anandrjs — Wed, 01 Jun 2011 16:14:29 GMT

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

gussfish — Wed, 01 Jun 2011 23:21:09 GMT

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

erichshiino — Wed, 01 Jun 2011 23:26:53 GMT

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

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