Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
lukeert19
Contributor III
Contributor III

count function linked to specific table [ in expression]

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]

lukeert19_0-1593554537312.png

 

 

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?

 

1 Solution

Accepted Solutions
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

4 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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])

 

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
lukeert19
Contributor III
Contributor III
Author

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 😞

 

masterCalendar.JPG

 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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
lukeert19
Contributor III
Contributor III
Author

Thanks your help