New Topic

Community Topic Problem Report Builder

David Registro

APAC SD Team

Category Report Builder

Product Version 7.4

Report left join on other View does not return the right data

Guest shared this problem ago

Resolved

Hello,

I create a report where I make a "left join" on an other view.. But the result is not correct. Please help :)

When I turn on "Show Duplicate Records", the result list is much more than if I disable it.

Files:

The same problem 0

Replies (11)

David Registro ●

Hi Carsten,

Apologies for the delays on this.

Left outer joins means, take everything from the left table, and show matching rows on the right, and any other rows returned.

So you will get rows matching, and also blank rows (from the right table) where they are not.

Then if you show duplicates, again it will impact the number of blank rows>

E.g. If duplicates now shows more on the left, you get more on the right.

Hope this makes sense and apologies if I'm missing something here.

Regards,

David

Reply URL

Guest ●

HI David, you are right. But I have on the right side the informations, but yf shows me empty fields. So there is something wrong. I use on the left side "Oracle-Tables" and on the right side "MSSQL-Tables".

Maybe we can look together on this issue.

Greets

Reply URL

David Registro ●

Hi Carsten,

If you believe data should be matching across both tables, the only way to prove would be to look at the matching data, data types, join type and output.

So can you please send across.

1. Table information from your left table. Do NOT join this, I want to see the table by itself.

This needs to show the columns, raw data (do no apply any formatting) and data type

2. Table information from your right table. Do NOT join this, I want to see the table by itself.

This needs to show the columns, raw data (do no apply any formatting) and data type

3. Some rows where the data should be matching, and where you would be expecting data.

By getting the above, I should be able to see the data from the computer name, identitat columns and see if in fact they should be matching, or if there is something else at play here.

Again, keep in mind this is a public post, so if the data is sensitive, please move this to a private ticket.

Thanks,

David

URL

Guest ●

Hi,

1. The "left" table is a sql with left joins. The join condition to the right table is the field"sd.Name0" (see xlsx "left table sd0.name0") We make a substring by the computer name, because all our assets starts with "A" so we replace the first Letter with an "A", see SQL

2. The right table informations.The join condition field is "Comp_ID"

Freundliche Grüße

Carsten Lempert

Files:

Reply URL

David Registro ●

Thanks Carsten, unfortunately this is not what I was expecting.

Here is the sort of info I'm after, that will really help me .

-Left table data (how it's returned in YF), also export to CSV

-Right table data (how it's returned in YF), also export to CSV

-How join is configured in Yellowfin

-Output of said join, with and without duplicates.

See attached examples.

Sorry for the 20 questions!

Regards,

David

Files:

URL

Guest ●

Hi no Problem,

here is the result. I have filtered the right table on "Identität" like 'A000G%', there are too much data in it.

Files:

URL

David Registro ●

Thanks Carsten!

Ok so using the spreadsheets in both excel and YF, I'm getting the same results.

I see same number of rows, and this does not change whether I turn suppress duplicates on or off. See attached screens.

What this tells me is that any duplicate rows you're seeing must be coming from your actual data set, which I assume comes from your view joins.

The report data you've sent to me works as expected.

I think before we go around in circles I'll reach out to a colleague in the UK and see if they can tee up a time with you to as this should be straight forward, and worry I might be missing something with your data set.

Another option, if I get a login to your system and this report, I can make a copy and check it out.

Sorry for the troubles with this.

Thanks,

David

Files:

URL

Guest ●

Hi, maybe it is a Problem, that the left table Looks on MSSQL and the right on Oracle? Yes it would be nice, when someone Looks on our live System. It is easier to explain ;)

URL

David Registro ●

You could be right, sorry for the delays on this, just reaching out to the UK team again. We will get back to you soon.

Regards,David

URL

Paul Thornton ●

Hi Carsten,

I would like to arrange a screen share with you so we can take a look at this. Can I suggest a time at around 3pm (U.K time) today or some time tomorrow afternoon?

Regards,

Paul

Reply URL

Guest ●

HI, it is 4pm german time. I think that's ok. Please send me an invite.

Reply URL

Paul Thornton ●

Hi Carsten,

We can bring this to 3pm german time if you like and it might be better for us also. If you can let me know which one suits and I will schedule the invite.

Thanks,

Paul

Reply URL

Guest ●

Ok, then I prefer 3:15pm ;)

Reply URL

Paul Thornton ●

Hi Carsten,

No problems. I have sent you the invite for 15:15 your time. There will be myself and Brett Churchill on the call.

Regards,

Paul

Reply URL

Paul Thornton ●

Hi Carsten,

Thank you for your time today and joining Bret and myself on the call. Just a quick recap.

From observing the issue first hand and through Bret's analysis we discovered that the matching was not occurring due to the number of rows being returned. After changing the maximum rows from 10,000 to no limit we found that the matching worked, so the solution was to remove this limit. As noticed, there was a performance change by doing this, so we also suggested a couple of things to improve this which you will now look at.

Let me know if you need anything else on this ticket or are happy for it to be closed off for now.

Regards,

Paul

Reply URL

Guest ●

Hi,

thanks for help. Everything is fine now. I filtered out something, and now the Report runs as espected :). You can Close this ticket.

Reply URL

Paul Thornton ●

Hi Carsten,

That is excellent news. I am closing off now, but please reach out if you need further help.

Regards,

Paul

Reply URL