topic Re: Crosstable --> $orphan in QlikView

Crosstable --> $orphan

Tue, 09 Mar 2010 19:15:26 GMT

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

Crosstable --> $orphan

Tue, 09 Mar 2010 19:44:59 GMT

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 ...

Crosstable --> $orphan

Tue, 09 Mar 2010 20:26:34 GMT

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

Crosstable --> $orphan

bradshields — Thu, 27 May 2010 05:10:57 GMT

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

Crosstable --> $orphan

erichshiino — Mon, 31 May 2010 19:59:40 GMT

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

Re: Crosstable --> $orphan

pablolabbe — Wed, 15 May 2013 18:34:09 GMT

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

Re: Crosstable --> $orphan

sebastiandperei — Sun, 23 Jun 2013 20:20:06 GMT

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

Re: Crosstable --> $orphan

iwqa2812 — Fri, 21 Mar 2014 10:40:15 GMT

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

Re: Crosstable --> $orphan

Fri, 15 Aug 2014 17:58:12 GMT

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 *;

LOAD * FROM 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

Re: Crosstable --> $orphan

Bruno_Calver — Tue, 09 Sep 2014 10:00:41 GMT

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

Re: Crosstable --> $orphan

Wed, 10 Sep 2014 08:39:53 GMT

Thank you Bruno – it’s great to know I’ve helped someone on the community since it is my first stop for help when I am stuck.

Kathryn Brown

Re: Crosstable --> $orphan

kangaroomac — Thu, 21 May 2015 00:00:43 GMT

Hi Brad,

Well done! It's quite ironic, because I've just had the same thing happening ("Too" optimized load) and a quick search resulted in your answer.

Now the irony (as you know) is that we were sitting in the same office at the time...

Re: Crosstable --> $orphan

Tue, 26 May 2015 19:47:30 GMT

Thank you Brad,

Greetings

Re: Crosstable --> $orphan

gallantabs — Tue, 05 Dec 2017 10:21:42 GMT

Three years on and your solution worked wonders for me on qliksense. Thanks

Re: Crosstable --> $orphan

Dano — Thu, 30 May 2019 07:47:12 GMT

Another solution is to use Resident Load rather than load data directly from QVD.

Re: Crosstable --> $orphan

neerajthakur — Tue, 21 Sep 2021 03:49:55 GMT

But why does it get created in the first place? What's the reason and does where 1=1 affects it to display correctly.