topic Confusion with Concatenate. in QlikView

Confusion with Concatenate.

Thu, 14 Apr 2011 10:31:24 GMT

Hi All,

I am a little bit confused with my results from a concatenate statement, I have the following and am not getting the result I expect. Could someone point out if my understanding of concatenate is incorrect or you think it is my code.


Tab1:
Load *,
InvoiceDate AS Cal_Date
from .....

concatenate load *,
SalesDate AS Cal_Date
from .....

Cal_Load:
load Cal_Date
from cal.QVD.

I was expecting to be able to select a date from the calendar and return all invoicedate & SalesDate record with the same date. This does not seem to be the case

Confusion with Concatenate.

Miguel_Angel_Baeyens — Thu, 14 Apr 2011 10:37:13 GMT

Hello,

I think that the best description of what concatenation does is that it "appends" records on the second lodaded table to the first one, resulting in only one bigger table by default, with as many records as the sum of records in table 1 and 2.

If you want only recors where SalesDate is equal to InvoiceDate, then use a EXISTS() function in the WHERE clause:

Invoices:Load *,InvoiceDate AS Cal_Datefrom ..... Sales:load *,SalesDate AS Cal_Datefrom .....WHERE EXISTS(InvoiceDate, SalesDate);

Hope this makes sense.

Confusion with Concatenate.

Thu, 14 Apr 2011 10:44:27 GMT

Thanks for your reply, I probably did not explain myself properly in my original post.

I would like to load in all sales and invoice data and when I select a date via my calendar (third table) I would like to see the invoice information and the sales information for that time period.

Confusion with Concatenate.

stephencredmond — Thu, 14 Apr 2011 10:48:14 GMT

Hi,

In theory, your code looks sound (without being able to see the data). You will have one table (Tab1) containing all your Invoices and all your Sales data. They may have different fields so, where there is no match, there will be null values, but there should always be a Cal_Date and, if you select a date, you will see all the Invoices and all the Sales for that date.

This is a common way of dealing with data like this for me.

What I might look at is putting a date field and Cal_Date in a table box with some of the fields from Tab1 and see if there is a mis-match. One of the common things is that one of the original date fields might have some time portion in it. I would normally use the Floor() function to make sure this is not the case, e.g.:

floor(SalesDate) As Cal_Date

Regards,

Stephen