Issue with data load from excel

[#1]

Hi,

I have a requirement to upload data from multiple excel files. Which is quite simple but the challenge is I only need to load July column from 2015Jul.xls , Aug column from 2015Aug.xls and from the last file I need to load all the columns after that month.

For example here the last month is Sep so from 2015Sep.xls we need to load all the columns from Sep.

So the resulting file should have data like that in the attached image.

The result image is just for illustration. I'm aware we'll need to load it as a cross table because we need to report on Forecast values and on the date from the resultant.

Can someone pls attach a sample qvw for this requirement.

Thanks in advance.

Regards,

Sachin

[satishkurra]

Do this

From the Date field, first extract MonthYear and then Use Match function in load script to load only that data

[#3]

But we need to do that with respect to file we are loading which I'm unable to do. Because monthyear are common in all the excel files.

And I'm doing a * load while loading the excel files. Since there are multiple files and new one keep adding every month.

[swuehl]

Maybe like this:

INPUT:

CROSSTABLE (YearMonth, Forecast, 3)

LOAD Date#(Left(Filename(),7),'YYYYMMM') as Source,

  Metric,

    Country,

    [42005],

    [42036],

    [42064],

    [42095],

    [42125],

    [42156],

    [42186],

    [42217],

    [42248],

    [42278],

    [42309],

    [42339]

FROM

[2015*.xlsx]

(ooxml, embedded labels, table is Data);

LOAD Metric & Country & YearMonth as TmpKey, *, exists(TmpKey ,Metric & Country & YearMonth) as flag;

LOAD Metric, Country, Monthname(num#(YearMonth)) as YearMonth, Forecast, Source

Resident INPUT

WHERE num#(YearMonth) >= Source and not exists(TmpKey ,Metric & Country & Monthname(num#(YearMonth)))

ORDER BY Source desc;

DROP TABLE INPUT;

DROP FIELDS flag, TmpKey;

[#5]

Hi Stefan,

I achieved the result in a slightly different way. But due to some reason two of the files provided by customer are not being recognized by QlikView. Can you suggest something.

Those files are 2015 Feb and 2015 Sep.

Regards,

Aditya

[swuehl]

Instead of the wildcard * in the FROM part, you can use something like

FOR Each File in filelist (vPathName &'*.xlsx')

LOAD

'$(File)' as Name,

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

NEXT File

and check if all file names have been read.

Anything special with these two files? Maybe different sheet names / no data contained?

[#7]

Hi Stefan,

Appreciate your help. I tried your part of code and it's returning all the files. In that case I think there is some issue with filebasename() function. The thing is, I need to use the date# in filebasename which I'm using in later part of my code.

But when I'm adding FileBaseName('$(File)') to your code its not returning anything.

[swuehl]

That's expected.

Most of the file functions work on the file currently being read in the table load, and there is no file source in an autogenerated table.

Your code will open the excel files and generate a record with filename and date per input record. I assume these two files don't pipe input records to your load, then you won't see records with the file name and date, too.

Have you tried opening the files with using the wizard? And have you tried using the wizard with one of the 'good' files? Do you see any differences in the generated code and preview?

[#9]

Yeah, I did that too. They look the same.

[swuehl]

Ok, then try to set up a common load statement for your excel files using the for each .. in filelist() method.

Try to load some data, concatenating everything in one table.