topic JOIN failing using a Crosstable in QlikView

JOIN failing using a Crosstable

cliff_clayman — Fri, 12 May 2017 21:07:51 GMT

I am trying to use the Crosstable function and then Join to another table i previously loaded. The join does not seem to be working as I am getting duplicate data. I've attached a sample of the QVW that is not working. I know I can load them as separate tables and then join to them, but I would like to know why I cannot get this to work using Crosstable.

Re: JOIN failing using a Crosstable

swuehl — Fri, 12 May 2017 22:39:35 GMT

I don't know your original data, but the CROSSTABLE LOAD transform your input file that maybe shows a single line per 'Supplier number-BS' to multiple lines (one for each column of your input file).

The Crosstable Load

If you then JOIN this table to another table by key 'Supplier number-BS', your records in this table will be duplicated.

That's how a JOIN works. Maybe you are missing another, additional key field?

Re: JOIN failing using a Crosstable

cliff_clayman — Fri, 12 May 2017 22:50:22 GMT

If I provided some data, could you take a look at it?

Re: JOIN failing using a Crosstable

rwunderlich — Fri, 12 May 2017 22:51:37 GMT

In your data, I see each "Supplier number-BS" has a row for each "Business State" generated by the crosstable, which is what I would expect.

The "Supplier number-BS"=0 has multiple rows for a state, but I expect that is because table Sheet1 had multiple rows for the "0" value.

-Rob

Re: JOIN failing using a Crosstable

swuehl — Sat, 13 May 2017 09:48:10 GMT

Cliff Clayman wrote:

If I provided some data, could you take a look at it?

Sure, that would be helpful.

Provide some sample data (your excel file or some INLINE data tables that show the same effect, the QVW should be reloadable).

And maybe point us to some records where your data gets duplicated and what you expect to see instead given your input files.

Re: JOIN failing using a Crosstable

swuehl — Sat, 13 May 2017 17:31:40 GMT

Ok.

And what do you expect to see e.g. for Contact SAP Vendor No.= 0?

Note that this field is not a primary key in your table.

Re: JOIN failing using a Crosstable

cliff_clayman — Sat, 13 May 2017 17:34:30 GMT

Yes, if the value is 0, then I expect to see 0.

Re: JOIN failing using a Crosstable

swuehl — Sat, 13 May 2017 18:24:10 GMT

No, that's not what I meant.

You were mentioning that data gets duplicated in your OP.

As far as I see, that's due to your key field not being a primary key, so you get multiple records in your first table joined with multiple records in your second table (for business states that show a true StateFlag for multiple lines).

The JOIN will create a resulting table with all combinations of first table records and second table records per key.

Does this sounds reasonable?

Or put it another way: you mentioned that you can get your expected result using a different script.

Could you post this script?

Re: JOIN failing using a Crosstable

swuehl — Sat, 13 May 2017 21:26:49 GMT

Are you maybe looking for something like attached?

I've used the record number as additional key for the JOIN, so a StateFlag only joins to the record where it was defined.