topic Re: JOIN And SUM not working in QlikView

JOIN And SUM not working

Mon, 18 Feb 2013 13:01:26 GMT

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?

Re: JOIN And SUM not working

martin59 — Mon, 18 Feb 2013 13:16:36 GMT

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

Re: JOIN And SUM not working

martin59 — Mon, 18 Feb 2013 13:17:07 GMT

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

Re: JOIN And SUM not working

Mon, 18 Feb 2013 13:34:43 GMT

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

Re: JOIN And SUM not working

Mon, 18 Feb 2013 13:43:46 GMT

Just drop the field FCL or give it different names in the 2 tables.

e.g.

Drop field FCL;

Re: JOIN And SUM not working

martin59 — Mon, 18 Feb 2013 15:53:44 GMT

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

Re: JOIN And SUM not working

martin59 — Mon, 18 Feb 2013 15:53:52 GMT

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

Re: JOIN And SUM not working

martin59 — Mon, 18 Feb 2013 15:54:01 GMT

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

Re: JOIN And SUM not working

Tue, 19 Feb 2013 08:00:04 GMT

It's working, thanks!