topic Re: Using the crosstable command in qliksense has created orphans in QlikView

Using the crosstable command in qliksense has created orphans

gallantabs — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using the crosstable command in qliksense has created orphans

sunny_talwar — Fri, 01 Dec 2017 17:01:48 GMT

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

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Fri, 01 Dec 2017 19:10:21 GMT

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

Re: Using the crosstable command in qliksense has created orphans

sunny_talwar — Fri, 01 Dec 2017 19:59:23 GMT

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?

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Fri, 01 Dec 2017 22:24:31 GMT

Thanks Sunny. Will be back with screenshots on monday

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Mon, 04 Dec 2017 10:07:09 GMT

Hi Sunny,

Please see screenshot below with orphan columns.

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Mon, 04 Dec 2017 10:17:26 GMT

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.

Re: Using the crosstable command in qliksense has created orphans

sunny_talwar — Mon, 04 Dec 2017 12:21:07 GMT

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)

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Mon, 04 Dec 2017 16:15:22 GMT

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

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Mon, 04 Dec 2017 18:29:08 GMT

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

Re: Using the crosstable command in qliksense has created orphans

sunny_talwar — Mon, 04 Dec 2017 19:03:32 GMT

Seems to be an issue with the variable here

Where do you declare varToday?

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Mon, 04 Dec 2017 21:38:46 GMT

Ooops! Will check it.

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Mon, 04 Dec 2017 22:14:19 GMT

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

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Mon, 04 Dec 2017 22:21:52 GMT

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

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Mon, 04 Dec 2017 22:55:36 GMT

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

Re: Using the crosstable command in qliksense has created orphans

gallantabs — Tue, 05 Dec 2017 17:22:42 GMT

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.