Join Pb

master_student — Wed, 25 Nov 2020 16:16:04 GMT

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

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.

Re: Join Pb

swuehl — Tue, 24 May 2016 10:13:48 GMT

Maybe have a look at

Fact Table with Mixed Granularity

and the referenced tech doc.

Re: Join Pb

master_student — Tue, 24 May 2016 11:25:50 GMT

The result of my actual join :

topic Re: Join Pb in QlikView

Join Pb

Re: Join Pb

Re: Join Pb