Using the crosstable command in qliksense has created orphans

[gallantabs]

Hi Experts,

I have used the crosstable command in the data load editor to load data from a table in excel into qlik sense (see below).

This works fine, however, when I looked at the data model viewer I noticed orphans for the columns. How do I resolve this please?

PT_Products:
Crosstable(Products,Values,3)

LOAD [MonthYear],

[Year],

[country],
[Apples],
[Oranges],
[Lemons]
 
FROM [lib://AttachedFiles/Dummy-SalesFile.xlsx]
(ooxml, embedded labels, table is [Sheet1]);

Thanks

[sunny_talwar]

Seems to be an issue with the variable here

Where do you declare varToday?

[gallantabs]

Ooops! Will check it.

[gallantabs]

Looking at Henric's loop through to load all files and folders, I am not sure that what I did is correct. How do I specify the crosstable command as the examples I looked at seem to load all files in a folder. I'd be honest I am stuck and would appreciate your help to crack this thank you

[gallantabs]

Is this all I need below? 

sub DoDir (Root)

   for each Ext in 'xlsx'

      for each File in filelist (Root&'\*.' &Ext)

         LOAD

            '$(File)' as Name,

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

            FileTime( '$(File)' ) as FileTime

         autogenerate 1;

      next File

   next Ext

   for each Dir in dirlist (Root&'\*' )

      call DoDir (C:\Users\aa\Downloads\Crosstableload)

   next Dir

end sub

call DoDir ('C:\Users\aa\Downloads\Crosstableload')

[gallantabs]

I have tried the above and the dir is null and no files were found.

[gallantabs]

Thanks Sunny for your help.  I checked the varToday variable which was being set in the data load editor.

I then came across an old post which used a different method to do a wildcard load from excel into a temp table and then load required columns using crosstable syntax. I will look into making the for-loop work at some stage, but I have gotten rid of the orphans.