Comparing sum before and after a date with boundary condition that sum before is >0

[mvanderaa]

I'm trying out Qliksense and I want to replicate a calculation that I have in a sql query. What I want to achieve is best explained by an example.

I have a table of error loggings, let's call it "E", and a table of parts that have been replaced on the machine population, lets call it "R". I want to look at the event occurence count before and after replacement of a certain part. The thing is, I only want to take the occurrence after into account if there are also occurrences before (per individual replacement row).

The time windows are (R.date - vWindow) and (R.date + vWindow), where vWindow is entered by the used in an input box. For clarity I color coded them blue and red for table E.

So for both event A and B each machine has a row with value occurences=10 that is not in the aggregation for "occurence after" because that event does not occur in the "before"  window.

Here is the data for copying purposes as well

E					R				Result
Date	Event	Occurrences	Machine		Date	Part number replaced	Machine		Event	Occurrences before	Occurrences after	Percentage after replacement
12-1-2020	B	2	1		14-1-2020	Z	1		A	3	1	33.3
13-1-2020	B	1	1		22-1-2020	Z	2		B	3	1	33.3
15-1-2020	A	10	1
15-1-2020	B	1	1
20-1-2020	A	2	2
20-1-2020	A	1	2
23-1-2020	A	1	2
23-1-2020	B	10	2

[vmusolov]

Hi!

I think you can try link tables using Machine only.

It means all EventDates will linked to all RepairDates.

After you can limit it in expression using (R.date - vWindow) and (R.date + vWindow).

In case of Machines can be repaired multiple times and have huge R.date and E.date amount, you can use IF and may be aggr() functions.

[Kushal_Chawda]

How you are getting occurrence after 1 for event A?

[mvanderaa]

Because the replacement that has the other 10 occurrences does not meet the condition that events before>0

The sql query would look like this (sorry for the lack of formatting here):

SELECT tbl1.[Event], SUM(tbl1.[Events Before]) AS [Events before final], SUM(tbl1.[Events after]) AS [Events after final]
FROM (

             SELECT e.[Event] ,e.[Date],e.[Machine]                 ===> start inner query
            ,CAST(CASE WHEN DATEDIFF(day,e.[Date],r.[Date])>0 AND DATEDIFF(day,e.[Date],r.[Date])<3 THEN SUM(e.Occurrences) else 0 end as INT) as [Events before]
             ,CAST(CASE WHEN DATEDIFF(day,e.[Date],r.[Date])<0 AND DATEDIFF(day,e.[Date],r.[Date])>-3 THEN SUM(e.Occurrences) else 0 end as INT) as [Events after]
              FROM [QS_replacements] as r INNER JOIN
                           (SELECT [Date], [Event], [Occurrences], [Machine]
                            FROM [QS_events]) AS e ON e.Machine=r.Machine
                           GROUP BY e.[Event], e.Machine, e.[Date], r.[Date]) as tbl1  ===> end inner query
GROUP BY tbl1.[Event], tbl1.[Machine]
HAVING SUM([Events before])>0

Output inner query

Query output

[Kushal_Chawda]

try below

R_Map:
mapping LOAD
    Machine1 as Machine,
    date(Date1) as Date1
FROM [lib://Data/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

E:
LOAD
    date("Date") as E_Date,
    Event as E_Event,
    ApplyMap('R_Map',Machine,0) as R_Date,
    Occurrences as E_Occurence,
    Machine
FROM [lib://Data/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

T1:
Load Machine,
    E_Event,
    E_Date,
    R_Date,
   sum(if(R_Date-E_Date>0 and R_Date-E_Date<3,E_Occurence,0)) as Events_before,
   sum(if(R_Date-E_Date<0 and R_Date-E_Date>-3,E_Occurence,0)) as Events_after
Resident E
Group by E_Event,Machine,E_Date,R_Date;

Drop Table E;

Final:
Load *
Where Events_before>0;
Load Machine,
    E_Event,
    sum(Events_before) as Events_before,
    sum(Events_after) as Events_after
Resident T1
Group by Machine,E_Event;

Drop Table T1;

[mvanderaa]

Hi Kush,

thanks for the suggestion. This works for me for this case, but I'm afraid I haven't been clear in my description. In the final app, the replacement table as it is showing in my example would be already filtered on a single part number (Z in this case). I was therefore was hoping to do it in an expression so that the user would choose a part number from a filter pane. Would your load script work if we make use of a dynamic view/on demand app generation?

Edit: the window size before and after would also be a variable to be set by the user, which I used 3 days for in this example.