Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Im using 2 tables with both the column FCL in it. The first column has an ID (KREDIETBEHEERDER) which i want to use as key of my fact table. The other one has an amount in it. An example.
VRNT_KREDIETBEW_SALDO:
KREDIETBEHEERDER FCL
1 100
2 200
3 896
4 976
FIS_VKREDIETBEWAKING.KREDIETBEHEERDER:
BEDRAG_BEGROOT FCL
10,00 100
20,00 100
30,00 896
40,00 976
What i want a fact table with the total amount per FCL, but the FCL must be stored in another table (the dimension). I made this script:
FactBudget:
LOAD
SUM(BEDRAG_BEGROOT) AS Total;
SQL SELECT FIS_VRNT_KREDIETBEW_SALDO.BEDRAG_BEGROOT, FIS_VKREDIETBEWAKING.KREDIETBEHEERDER, FIS_VKREDIETBEWAKING.FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING, BITFIS.FIS_VRNT_KREDIETBEW_SALDO
WHERE FIS_VKREDIETBEWAKING.FCL = FIS_VRNT_KREDIETBEW_SALDO.FCL
GROUP BY FCL;
DimBudget:
SQL SELECT FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING
This isn't working, i can't get the total BEDRAG_BEGROOT per FCL.
Does anyone know how to make it work?
Ok, so you have to do this :
FactBudget:
SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,
a.KREDIETBEHEERDER
FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.KREDIETBEHEERDER;
DimBudget:
SQL SELECT KREDIETBEHEERDER,
FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING
Hope that helps you
Martin Favier
Hi,
You have to do this :
FactBudget:
SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,
a.FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.FCL;
DimBudget:
SQL SELECT FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING
Hope that helps you,
Martin Favier
Hi,
You have to do this :
FactBudget:
SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,
a.FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.FCL;
DimBudget:
SQL SELECT FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING
Hope that helps you,
Martin Favier
Thanks for your quick response.
Thats indeed working, but FCL turns out to be a key. KREDIETBEHEERDER must be the only key in the two tables.
Ike this:
FactBudget:
KREDIETBEHEERDER (KEY)
Total
DimBudget
KREDIETBEHEERDER (KEY)
FCL
Just drop the field FCL or give it different names in the 2 tables.
e.g.
Drop field FCL;
Ok, so you have to do this :
FactBudget:
SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,
a.KREDIETBEHEERDER
FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.KREDIETBEHEERDER;
DimBudget:
SQL SELECT KREDIETBEHEERDER,
FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING
Hope that helps you
Martin Favier
Ok, so you have to do this :
FactBudget:
SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,
a.KREDIETBEHEERDER
FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.KREDIETBEHEERDER;
DimBudget:
SQL SELECT KREDIETBEHEERDER,
FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING
Hope that helps you
Martin Favier
Ok, so you have to do this :
FactBudget:
SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,
a.KREDIETBEHEERDER
FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.KREDIETBEHEERDER;
DimBudget:
SQL SELECT KREDIETBEHEERDER,
FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING
Hope that helps you
Martin Favier
It's working, thanks!