Mapping a Date field with multiple values

[divya_anand]

Hi,

This is the current scenario in my datamodel.

Bridge Table is the product of mapping Date2 & Date3 into 1 field %Date.

How do I also include Date1 into the field %Date in the BridgeTable with Type=1?

I understand Mapping load similar to what was done for Date2 & Date3 should work.

However, Every ID in Table 2 has single value for Date2 & Date3 which is not the case in Table1.

Date1 has multiple values for each ID in Table1.

Any Suggestions on this?

Attached below is a sample qvw.

Kindly let me know if more details are needed in understanding this.

Thank you.

[enriquem]

Hi,

To include Date1 into %Date you must load your data from Date1 and concatenate to the table BridgeTable and put here the Dates registers.

Concatenate

BridgeTable:

Load Date1 as %Date

resident your_Table;// from your_Table;

Remember, that it's important do it inmediatly that you build your BridgeTable, or the concantenate wouldn't be necesary.

Regards

[divya_anand]

Hi Enrique,

Thank you for the response.

If I concatenate like you've mentioned, %Date is having all possible values. But how do I come up with an output as shown in picture2?

[enriquem]

Hi,

Sorry, You must concatenate the fields considering the ID and Date1, so when you do de load you must add:

Concatenate

BridgeTable:

Load

    Distinct Autonumber(Date1) as ID, // or whatever_you_do_to_get_the_ID as ID

    Date1 as %Date

resident your_Table;// from your_Table;

Regars.

[enriquem]

Hope that the last post help you,

I forgot tell you that with the DISTINCT keyword you will put all the Date1 that don't have your BridgeTable, so pay attention that you don't have duplicated values, and the result of Dates will be the junction of the Date1 and Date2.

Regards

[divya_anand]

Hi Enrique,

I really appreciate your time & efforts.

Do you mean I should use Autonumber ?

Would be really nice if you could use the qvw that I've attached and show how to do this.

Thank you.

[enriquem]

Hi,

You must use your previous ID, if you were using the AUTONUMBER function you must use it again. In other hand if you are using an ID field you must use this ID fiel again, so you must use whatever you were using.

I tell you about Autonumber function because it's more efficient for the fields that relation the tables for your future querys, but you can use an id or the date directly.

I will download your qvw and try to give you a solutions

Regards.

[enriquem]

Hi,

First, I must understand your model, so what means the ID of the Table1 and Table2 and its dates? Because if the ID don't have relationship to the Date it's one important point.

I will put my example about how i will resolve your problem, in this model you have a bridge table that connect the table1 and table2 (I suppose that there are the fact table) and i create a calendar between their dates. Now if you apply a filter in the fields on Calendar dimension you will see the corresponding records from Table1 and Table2.

Maybe this isn't an easy solutions, but it's work and it's an easy way to understand without Maps.

Regards