loading values from different tables

[#1]

Hi Everyone-

I am trying to create an expression in a chart that chooses the most recent date from a series of 3 date fields (Date1, Date2, Date3).

Date1 and Date2 are in table1, and Date3 is in table 2.  I've been trying to create a new table that contains the three dates, but have been unsuccessful thus far.

Can anyone advise as to the best way to go about getting the expression to work?

Thanks!

[manideep78]

Hi,

If your tables contain any key then by using left join you can create a new table which contains three date fields. Below is the example.

Now you can write an expression to get latest dates from three date fields. Let me know if this helps you or not. Its better if you provide sample data to know your requirement and explain what expression you need

Tab1:

LOAD * INLINE [

    ID, Date1, Date2

    1, 02/03/2013, 02/03/2013

    2, 03/03/2013, 03/03/2013

    3, 04/03/2013, 04/03/2013

];

Tab2:

Left Join(Tab1)

LOAD * INLINE [

    ID, Date3

    1, 02/03/2013

    2, 03/03/2013

    3, 04/03/2013

];

[Anonymous · Accepted solution]

Hi,

If you just want a table that contains the three dates, you can simply do:

DATES:

LOAD

     Date1,

     Date1 as Date

RESIDENT

     THE_TABLE_WHERE_YOU_GET_DATE1&2_FROM

;

CONCATENATE

(DATES)

LOAD

     Date2,

     Date2 as Date

RESIDENT

     THE_TABLE_WHERE_YOU_GET_DATE1&2_FROM

;

CONCATENATE

(DATES)

LOAD

     Date3,

     Date3 as Date

RESIDENT

     THE_TABLE_WHERE_YOU_GET_DATE3_FROM

;

You will then have all you dates in the same table.

But I think you then want to make some analyses with these dates, so you'd better create a table CALENDAR from the dates.

It all depends on your model and on what you want to do, so explain more about your needs if you need more help.

[eddysanchez]

Hi,

For join this information in one table you need to drop one of them

For example if your first table is named 'Table1' and the other is 'Table2'

with:

*********************************

left join (Table1)

load * Resident Table2;

drop table Table2;

*********************************

you gained join in Table1 the three dates and Table2 is dropped

[#5]

This suggestion helped to create the other table.  Now, the problem is that the tables are creating a loop when i load all of the data, as well as the synthetic key for Date1 and Date2.

[Anonymous]

Can you show your qvd or your model ?

[#7]

I had created another table with the calendar that had the same name.  once i switched the name of the calendar, it worked.

Thanks for the suggestion