topic Issue with data load from excel in QlikView

Issue with data load from excel

Wed, 04 Nov 2015 22:04:07 GMT

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

Re: Issue with data load from excel

satishkurra — Wed, 04 Nov 2015 22:14:58 GMT

Do this

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

Re: Issue with data load from excel

Wed, 04 Nov 2015 22:18:38 GMT

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.

Re: Issue with data load from excel

swuehl — Wed, 04 Nov 2015 22:55:38 GMT

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;

Re: Issue with data load from excel

Thu, 05 Nov 2015 16:12:05 GMT

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

Re: Issue with data load from excel

swuehl — Thu, 05 Nov 2015 16:21:49 GMT

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?

Re: Issue with data load from excel

Thu, 05 Nov 2015 16:47:48 GMT

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.

Re: Issue with data load from excel

swuehl — Thu, 05 Nov 2015 16:53:30 GMT

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?

Re: Issue with data load from excel

Thu, 05 Nov 2015 17:12:04 GMT

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

Re: Issue with data load from excel

swuehl — Thu, 05 Nov 2015 17:16:04 GMT

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.