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]

Are you doing a wild card load with CrossTable? If that is what you are doing, then this won't work... because CrossTable doesn't work too well with wild card load... in that case it's better to use a for loop to load the multiple files

[gallantabs]

Hi Sunny,

Thank you for replying, really appreciate it. I see you on a lot of questions/queries helping people out.

In response, I have not done a wildcard load. the little section of code below is similar to what I used.

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

[sunny_talwar]

This enough should not create any orphan tables... that is strange that it does... can you share the data model where you see the orphan tables?

[gallantabs]

Thanks Sunny. Will be back with screenshots on monday

[gallantabs]

Hi Sunny,

Please see screenshot below with orphan columns.

[gallantabs]

There are 2 tables that I am loading with the crosstable command and I can now see that I may need to use the for loop as there are multiple tables and would appreciate assistance if this is the case.

I await your reply.

[sunny_talwar]

Here are some resources on the topic of loops

loop through to load all files from a folder and its subfolders?‌

For each..next ‒ Qlik Sense‌ (look at the second example under Examples)

[gallantabs]

Hi Sunny, I am looking through now and will get back to you...Thanks

[gallantabs]

I placed the xlsx files in a folder called 'C:\Users\aa\Downloads\Crosstableload' and used the code 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')

Products:
Crosstable(Products,TP_BB_RetailVal,5)

LOAD [MonthYear],
[Year],
[Country],
[Overdr],
[Personals],
[Amorns],
[Cheqhase],
[Trde],
[Pledge],
[Leaing],
[Moges],
[Refing],
[Others]

FROM [lib://AttachedFiles/Dummy-Portfolio.xlsx]
(ooxml, embedded labels, table is [Portfolio Trends])

;

[Performance]:
Crosstable(Products,Values,11)

LOAD
[Year],
[Country],
[Month],
     Date([Month],'MM') as MonthP,
     Ceil(Month (Month)/3) as Quarter,
     InYearToDate(Month,$(varToday),0)*-1 as CurYTDFlag,
     InYearToDate(Month,$(varToday),-1)*-1 as LastYTDFlag,
[Type] ,
[Mont_Name],
[Month_Sort],
[Month_Year],
[qAdvances_Promy_Notes_Fees],
[qAdvan_Promiotes_LOC],
[sAdvances_Prootes],
[dAdvances_Prory_Notes_NIM_eceivables_],
[zAdvances_Promisory_Notes_R],
[cAdvances_Promiry_Notes_Rles],
[vAdva_Notes_Revenue],
[Ting_Income]
   
FROM [lib://AttachedFiles/FinancePerformance.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

and I get the error below

Any ideas what I have done wrong? Thanks again