count function linked to specific table [ in expression]

[lukeert19]

Hello gays,

I have a question, in picture below I have two different fields:

• [GoodsIn]
  • this field is loaded from TableB
• [repaired]
  • this field is loaded from TableB

[FinalTable]

below I have created the Mater Calendar from an existing table [ImpitCalendar]:

Tmp:
LOAD
min(RMA_ReceivingDate) as MinDate,
max(Today(1)) as MaxDate
RESIDENT ImpitCalendar;

LET Start = floor(YearStart(peek('MinDate')));
LET End = floor(YearEnd(peek('MaxDate')));

LET NumOfDays = End - Start + 1;

Date_src:
LOAD
date(floor($(Start) + Rowno() -1)) as DateId
AUTOGENERATE $(NumOfDays);

Drop Table Tmp;
Drop Table ImpitCalendar;

after the code above I have loaded two  different tables

[TableA]

[TableB]

how can I count the values by specifying the tables  and at the same time link them to MasterCalendar?

[Lech_Miszkiewicz]

Hi,

during load script create fact table by:

• loading Table A 
  • during load create flag goodsin=1
• concatenate Table B to table A
  • during this load create flag repaired=1

After that create your master calendar based on dates from concatenated tables.

Use expressions

• for goodsin:
  • count({<goodsin={1}>} [whatever you want to count])
• for repaired:
  • count({<repaired={1}>} [whatever you want to count])

[lukeert19]

Hello @Lech_Miszkiewicz 

I have written the code following your extraction, but seems doesn't work properly, or better "DateId" inside the "MasterCalendar" table seems not be linked correctly 😞

 

 Script:

//received
GoodsIn:
load   date(floor(ReceivingDate)) as ReceivingDate
      ,If(IsNull(ReceivingDate),'T','F') as ReceivingDateIsItNull
from
[------------]
(qvd);

//shipped
Concatenate
load
       If(IsNull(Delivery),'T','F') as DeliveryIsItNull
      ,date(floor(Shipping_date)) as Shipping_date
from
[------------]
(qvd);


//master calendar --> date from GoodIn table
TmpDate:
LOAD
min(ReceivingDate) as MinDate,
max(today(1)) as MaxDate
RESIDENT GoodsIn;

LET Start = floor(YearStart(peek('MinDate')));
LET End = floor(YearEnd(peek('MaxDate')));

LET NumOfDays = End - Start + 1;

MasterCalendar:
LOAD
 date(floor($(Start) + Rowno() -1)) as DateId
AUTOGENERATE $(NumOfDays);

Drop Table TmpDate;

my control is based if field is null or not, by this way I can check if one specific item is arrived and also if is shipped
in expression I have used:

count({<ReceivingDateIsItNull={'F'}>}ReceivingDateIsItNull) --> GoodsIn

Count({<DeliveryIsItNull={'F'}>}DeliveryIsItNull) --> shipped

Thanks in advance @Lech_Miszkiewicz 

[Lech_Miszkiewicz · Accepted solution]

How do you link your dates to master calendar.

based on your script there is no link as you have:

• ReceivingDate
• Shipping_date
• and DateId

are you intending to use Master calendar to control both of those dates? If so, during load just rename both of them to DateId

[lukeert19]

Thanks your help