Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

cliff_clayman
New Contributor III

JOIN failing using a Crosstable

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.

Tags (2)
8 Replies
MVP
MVP

Re: JOIN failing using a Crosstable

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?

cliff_clayman
New Contributor III

Re: JOIN failing using a Crosstable

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

Re: JOIN failing using a Crosstable

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

MVP
MVP

Re: JOIN failing using a Crosstable

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.

MVP
MVP

Re: JOIN failing using a Crosstable

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.

cliff_clayman
New Contributor III

Re: JOIN failing using a Crosstable

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

MVP
MVP

Re: JOIN failing using a Crosstable

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?

MVP
MVP

Re: JOIN failing using a Crosstable

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.

Community Browser