Problem to display correct data in pivot table.

[jim_chan]

Hi guys,

I have having a problem to show what client wants on the pivot table.

the data is made up from 2 tables. using concatenate.

table1

record_id

business

year

version

concatenate(table1)

table2

record_id

business

year

version

details from table2

So. after loaded, i managed to get the data. like ID 4611, but it will also provide me another row of empty data in details_from_table2 field. for this line, I just want to show field with data only.

But, client also wants to see ID that has no data in details from table2.  for example ID 4610. I want to show this line too.

i have tried checking suppress/omit null value. but it will show me IDs that has data in details_from_table2.

i have also using expression like count( distinct record_id), thought this would help me to distinct the record_id.

What should i do in the expression?

rgds

Jim

[shawn-qv]

Do you have multiple records of the same record_id in table2 (i.e. 4611), where some have a details value and some are missing?

I think you need to better define the requirements.

S.

[jonathandienst · Accepted solution]

Concatenating like that will create at least one record without details (when it exists in table 1), and one record with details (when it exists in source 2). So what you are seeing in the pivot is what you loaded.

If you mean to add the details from source 2 to the rows already loaded from source 1, then you need a join and not a concatenate (which adds extra rows). Read up on JOIN in the manual for more details.

[jim_chan]

I have used left join().

but it burst my original records.