join two fact tables in data loader

[Ethel]

Hello community,

could you please help me with data loader. My problem seams simple , but I cannot solve it.

I have several dimensions and a fact table1 in data loader. One of the dimensions is date dimension(dimPeriod), it has Period Year, Period Month, PeriodID, Time frame and other fields. Time frame is a field that shows if it's a previous year or current year. Fact table1 has different keys, including PeriodID and some measures.  I have another Fact table2 that has fields Year, Month, some measures. I need to join these two  Fact tables in data loader.(I need to have the same dates through out the report)

I'm doing it this way, but maybe it's wrong?(It creates synthetic key, can I avoid it?)

in the fact table2 I do:

LOAD

....
"Year" as PeriodYear,
FiscalMonthNum as PeriodMonth,
...
FROM ...(qvd);

Left join(dimPeriod)
load
PeriodID,
Resident dimPeriod

My problem is that  my report that uses data from Fact table2 doesn't react to the "Time frame" field from dimPeriod. But tt reacts to the Period Year and PeriodMonth. 

Why? What can I change?

Thank you very much in advance!!!

[ckarras22]

Hello,

you can use applymap as in the code attached. Somehow remove from Facts the Year and Month (renaming, droping etc)

periodID:
load * inline [

PID, 	YEARA, 	MONTHA, TIME_FRAME
1,	 	2022,	02,		T1
2,		2022,	03,		T1
3,		2022,	05,		T1
4,		2022,	07,		T1
5,		2022,	09,		T1
6,		2022,	10,		T1
7,		2023,	01,		T2
8,		2023,	03,		T2
9,		2023,	07,		T2

];



periodID_MAP:
MAPPING LOAD 
YEARA&MONTHA,
PID
RESIDENT periodID;


FACT2:
Load
CUSTMR,
ApplyMap('periodID_MAP',YEARA&MONTHA) AS PID
;
LOAD * INLINE [

CUSTMR,	YEARA, 	MONTHA
C1,		2022,		02
C1,		2022,		05
C1,		2022,		09
C1,		2022,		10
C1,		2023,		03

];

[Ethel]

Hello, thank you so much! 

I cannot use inline. I'm loading data from qvd files.  In data loader my sections look like this:

Main.

Fact1

  LOAD

  PeriodID,

   ....

amount1

from 1.qvd 

dimPeriod:

LOAD
  PeriodYear,
  PeriodMonth,

  TimeFrame,
  PeriodType
  ....

from period.qvd

Fact2

  LOAD
  "Year" as PeriodYear,
  FiscalMonthNum as PeriodMonth,

amount2
from 2.qvd

Left join(dimPeriod)
load
  PeriodID
Resident dimPeriod

For some reason when I'm creating charts with amount2 and use PeriodYear and PeriodMonth, everything works fine. But it doesn't "react" on period type and   timeframe. I'm probably doing something wrong...

Thank you very much for looking into it.

[ckarras22]

Hello

My example is only to see the steps you should follow. Please try to replicate this and you will have results.

Also this piece of code is reduntant

Left join(dimPeriod)
load
  PeriodID
Resident dimPeriod

[Ethel]

Thank you so much!

I got the idea. But it's not working with my data. 

Could you please help me to figure out why it's not working in my case? This is my code

dimPeriod:

LOAD
PeriodYear,
PeriodMonth,
TimeFrame,
PeriodType,
PeriodID

FROM
[Period.qvd] (qvd)
;

period_MAP:
MAPPING LOAD
PeriodYear&PeriodMonth,
PeriodID
RESIDENT dimPeriod;

Fact2:

LOAD
ApplyMap('period_MAP',PeriodYear&PeriodMonth) AS PeriodID,
Amount
FROM [2.qvd](qvd);

 The error that I'm getting is that 

The following error occurred:

Field 'PeriodYear' not found

The error occurred here:

LOAD ApplyMap('period_MAP',PeriodYear&PeriodMonth) AS PeriodID , Amount FROM [2.qvd] (qvd)

Also for some reason, when I'm checking here my dimPeriod it doesn't show data, but qvd file has data and it's working fine in other codes. 

Thank you very much in advance!!

[ckarras22]

Hello

Because 2.qvd has   "Year"  & FiscalMonthNum, not PeriodYear & PeriodMonth

[Ethel]

You are right, but I was doing  "Year" as PeriodYear.  Could you please tell me how should I rewrite my code?  

[ckarras22 · Accepted solution]

Just replace

ApplyMap('period_MAP',PeriodYear&PeriodMonth) AS PeriodID

with 

ApplyMap('period_MAP',Year&FiscalMonthNum) AS PeriodID

Note:

PeriodYear  and PeriodMonth names, can be used in a preceding load or resident loading, later in your code