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
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);
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.
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.:
.png "Miguel_Angel_Baeyens"){kind=avatar}
