Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Crosstable --> $orphan

Hi

I want to create a crosstable, I've done it but when I take a look with the table viewer every field looks like a separate table with the &orphan and the name of the table as a prefix.

This's how I've done it:

INVENTORY_FAILURE2:
QUALIFY *;
UNQUALIFY CO_PRODUCT;
CROSSTABLE(Failure, Veces, 7)
LOAD co_enterprise,
co_art AS CO_PRODUCT,
DATE,
date(DATE) as Fecha_Inv,
year(date(DATE)) as Anno_Inv,
month(date(DATE)) as Mes_Inv,
day(date(DATE)) as Dia_Inv,
FAILURE1,
FAILURE2,
FAILURE3,
FAILURE4,
FAILURE5
FROM QvD\INVENTORY_FAILURE.QVD (qvd);

What am I doing wrong???

Thxs in advance

1 Solution

Accepted Solutions
bradshields
Partner - Creator
Partner - Creator

I had the same problem. I suspect it is a similar problem to trying to use a qvd in a mapping load. The easiest way to fix is to add something that stops the load of the qvd being optimized. I added "Where 1=1;" to the script of the crosstable load and it removed all the $orphan fields.

Cheers Brad

View solution in original post

15 Replies
Not applicable
Author

I think cross tab wizard in broken in 9.00.7440
I can not get it to work with even a very simple example. ( Spreadsheet attached.)

Assuming you want everything in the file I suggest you do it as follows:

CROSSTABLE(Failure, Veces, 7)

Load *
From ...

Not applicable
Author

Well, I didn't notice that the crossed table it was created, but still the problem with the &orphan.

Besides, I've a problem with the same table, when I qualify it, the name of the table it's written twice. Why that happens???

Thxs

bradshields
Partner - Creator
Partner - Creator

I had the same problem. I suspect it is a similar problem to trying to use a qvd in a mapping load. The easiest way to fix is to add something that stops the load of the qvd being optimized. I added "Where 1=1;" to the script of the crosstable load and it removed all the $orphan fields.

Cheers Brad

erichshiino
Partner - Master
Partner - Master

Thanks Brad, I had the same problem and your suggestion solved it!

pablolabbe
Luminary Alumni
Luminary Alumni

Hi Guys,

   I found the same behavior using version 11 SR1 and applied the same workaround to disable optimized load to correct this issue.

Thanks,

Pablo Labbe Ibaceta

sebastiandperei
Specialist
Specialist

WOWWWWWW This works!, it is incredible. Congratulations Brad, your suggest works very good!!!

iwqa2812
Contributor
Contributor

You're a genius Brad. It took me 2 days to work out why all those orphan fields were appearing, the preview of the actual crosstable looked correct. Couldn't figure out what I was doing wrong.

Cheers,

Ivana

Not applicable
Author

Hi Brad

I couldn't get your solution to work - no idea why - but I found a less elegant solution I thought I'd share in case anyone else has the same problem.

 

QUALIFY *;

a:

LOADFROM xyz.xls....;

UNQUALIFY *;

b:

CrossTable(Fieldname, Data, column ref)

LOAD * Resident a;

Drop table a;

I didn't get any orphans

Took me a while to come up with this though

Kathryn

Bruno_Calver
Former Employee
Former Employee

Hi Kathryn,

Great solution. The reason Brad's did not work in this case is all to do with the Qualify statement as far as I can tell. Because the field names in the source data are different to the qualified names I think the CrossTable function gets in a twist and then creates the orphans. Anyway this worked great

Bruno