Join Pb

[master_student]

Guys, I really need your help..

I have a budget file (excel) that contains the Budget by departement by month start of date :

Mois	LB	Budget
01/02/2016	achat_radio	180
01/10/2016	achat_radio	180
01/11/2016	achat_radio	180
01/07/2016	achat_radio	200

I have a second table that contains the amout approved by date (calendar date)

Date_DA	LB	MONTANT DA
10/01/2016	public_relations	4167
18/01/2016	operational_merchandising_costs	460

the problem is that there is no join between those two tables by the field date:

Date_DA	Mois	LB	Budget
-	01/02/2016	achat_radio	180
-	01/10/2016	achat_radio	180
-	01/11/2016	achat_radio	180

A part of my script is :

NoConcatenate

NoConcatenate

F_SUIVI_DA:

LOAD *,

trim(lower(LB2)) as LB,

AutoNumber( MakeDate( year(date([Date_DA],'DD/MM/YYYY')), Month(date([Date_DA],'DD/MM/YYYY')), day(date([Date_DA],'DD/MM/YYYY')) ) , '%Date ID') as  [%Date ID]

Resident F_SUIVI_DA_TMP;

NoConcatenate

SUIVI_DA:

load *,

'DA' as Flag,

ApplyMap('maptable',LB) as LBopex

resident F_SUIVI_DA;

NoConcatenate

PO_BUDGET:

LOAD   AutoNumber(Date(Mois, 'DD/MM/YYYY'), '%Date ID') as  [%Date ID],

     LBopex,

    CDR

       , Mois

      ,[Budget PO]

Resident PO_BUDGET_TMP;

//Budget par jour

NoConcatenate

PO_BUDGET_TMP2:

LOAd *

Resident PO_BUDGET_TMP;

left join(PO_BUDGET_TMP2)

LOAD   [Month start date] as Mois

        ,[%Date ID]

Resident D_Calendar;

NoConcatenate

PO_BUDGET_FCT:

LOAD

   [%Date ID],

      Mois

     ,0 as [Budget PO]

Resident PO_BUDGET_TMP2 ;

LOAD   [%Date ID],

     ,LB

        ,Mois

      ,[Budget PO]

Resident PO_BUDGET;

my data model :

Any help please.

[swuehl]

Maybe have a look at

Fact Table with Mixed Granularity

and the referenced tech doc.

[master_student]

The result of my actual join :