topic Null Name After JOIN in QlikView

Null Name After JOIN

Tue, 31 Jul 2012 23:26:41 GMT

Hello,

I have this simple two files joined on TYPE & SEND_DATE.

However, when joined, one of the record have two rows when looking at two metrics (one from each of the two data sources).

Email_Send is one metric

Order is another metric.

I have included two files for you to look into in details.

QVNullNameJOIN.jpg This screenshot shows the deplicate rows (I need to have them combined).

LAW.qvw is the actual file.

If you look at other Name, Email_Send & Order are combined into one row. I am suspecting SEND_DATE format is NOT exactly the smae causing the join to have duplicates. Can someone give me a pointer on how to join on exact format? Maybe need to convert to string and then join is safer?

This is a rather urgent report, so if anyone could help out, I would really appreciate it.

Sincerely!

Re: Null Name After JOIN

Wed, 01 Aug 2012 00:49:42 GMT

I tried converting the field to string using text() function.

From the image above, I notice the date fields are converted differently. One data source is converting to 41116.4625 and the other data source is converting to 41116. Is there a way that I can convert them to exact same format before the JOIN so no duplicate rows will return in my qlikview report?

Can anyone shed some light?

Re: Null Name After JOIN

Lee_Matthews — Wed, 01 Aug 2012 01:36:30 GMT

I assume from the above you are trying to join on two date fields. It looks like one field has the time in the date value as well, resulting in the decimal places. Just convert the dates to number using num() or num#() (I never know which to use so just try both) and then use the floor() function to round down to the nearest integer.