Skip to main content
Announcements
Join us on Feb.12 to Discover what’s possible with embedded analytics: REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
alespooletto
Creator II
Creator II

Filter data for a specific work day and count in the load editor

Hello, I want to have a table (or to write it in a chart as well, that would be nice too) which filters the original fact table with only data that has a count of 25 distinct SFC for the day and more ( > 25) and it should be only Monday to Friday. 

This is what I tried, but even if the load editor doesnt give me errors, i cant see the resulting FilterActiivtyLog table:

ActivityLog:
LOAD
    ACTION_CODE,
    ACTIVITY_LOG_USER as User,
    Date(Floor(ACTIVITY_LOG_DATE_TIME)) as Date,
    ConvertToLocalTime(Timestamp(ACTIVITY_LOG_DATE_TIME), 'Rome') as DateTime,
    SFC,  // Replace .01. and .02. with .00.
    Replace(SFC, SubField(SFC, '.', 2), '00') as OverallSFC,
    If(SubField(SFC,'.',2)='00','Main SFC','Sub-SFC') as MainSFC,
    ACTIVITY_LOG_RESRCE as Resource,
    ACTIVITY_LOG_WORK_CENTER as WorkCenter
FROM [lib://SEWQVD_Streams_ME_Global_Prov_4000/AssemblyData/ACTIVITY_LOG.qvd](qvd)
WHERE 
    Year(Date(Floor(ACTIVITY_LOG_DATE_TIME))) >= Year(Today()) - 2  // Storico di 3 anni da Gennaio
    AND WildMatch(ACTION_CODE, 'COMPLETE', 'LABOR_ON', 'LABOR_OFF', 'REWORK');

TempActivityLog:
LOAD
    Date,
    Count(DISTINCT SFC) as DistinctSFCCount
RESIDENT ActivityLog
WHERE WeekDay(Date) >= 0 AND WeekDay(Date) <= 4 
GROUP BY Date;

FilteredActivityLog:
LOAD
    *
RESIDENT ActivityLog
WHERE Exists(Date, Date)
AND Exists(Date, Peek('Date', 0, 'TempActivityLog')); 

DROP TABLE TempActivityLog;

Another thing I tried, a bit convoluted, was to do it like this directly in the KPI measure I needed: 

If(
    Count({< 
        ACTION_CODE = {'COMPLETE'}, 
        WorkCenter = {'P_F01','P_F02'}, 
        Day = {"=Match(DayOfWeek, 'Lunedì', 'Martedì','Mercoledì', 'Giovedì', 'Venerdì'))"} 
    >} distinct SFC) >= 25,
    Count({< 
        ACTION_CODE = {'COMPLETE'}, 
        WorkCenter = {'P_F01','P_F02'}, 
        Day = {"=Match(DayOfWeek, 'Lunedì', 'Martedì','Mercoledì', 'Giovedì', 'Venerdì')"} 
    >} distinct SFC)
)

 

Hopefully someone can help me out with this, it should be trivial but I can't make it work. Thanks

Labels (1)
1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

You didn't see the resulting table because Qlik by Default Concatenates the tables with the same number of fields.

As in your script. 

The "TempActivityLog" and "FilteredActivityLog" table contain the same field, so the "FilteredActivityLog" table is concatenated with the "TempActivityLog" table.

And then in the last statement, you dropped the "TempActivityLog" table, and thus you don't see the resulting table.

To forcefully stop the concatenation, you can use a keyword like "NoConcatenate" as shown below.

FilteredActivityLog:
Noconcatenate
LOAD
*
RESIDENT ActivityLog
WHERE Exists(Date, Date)
AND Exists(Date, Peek('Date', 0, 'TempActivityLog'));

 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

10 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

You didn't see the resulting table because Qlik by Default Concatenates the tables with the same number of fields.

As in your script. 

The "TempActivityLog" and "FilteredActivityLog" table contain the same field, so the "FilteredActivityLog" table is concatenated with the "TempActivityLog" table.

And then in the last statement, you dropped the "TempActivityLog" table, and thus you don't see the resulting table.

To forcefully stop the concatenation, you can use a keyword like "NoConcatenate" as shown below.

FilteredActivityLog:
Noconcatenate
LOAD
*
RESIDENT ActivityLog
WHERE Exists(Date, Date)
AND Exists(Date, Peek('Date', 0, 'TempActivityLog'));

 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Clement15
Partner - Specialist
Partner - Specialist

Hello,

I don't know your data but your set analysis seems not to have any obvious errors. If your table does not appear in the script you can try adding NoConcatenate between the script of each table.

alespooletto
Creator II
Creator II
Author

Yeah, this was the culprit. Also, unfortunately, I can't see changes in the data, and I suspect it's because in the calculation I do later in the chart that Qlik doesn't count those days with 0 counts of SFC made.

marcus_sommer

I'm not sure if the two exists-clauses to the filter-table are expedient. Personally I would skip this step and just keeping the count-table and then using an expression like:

Count({< 
        ACTION_CODE = {'COMPLETE'}, 
        WorkCenter = {'P_F01','P_F02'}, 
        DistinctSFCCount = {">=25"} 
    >} distinct MyField)

 

alespooletto
Creator II
Creator II
Author

Hello, thanks for your answer and sorry for my late response. I am trying to work it out as you explain here, but it seems the average still counts days as meaningful towards the average. Could you please tell me if this is the supposed behavior?

Measure:

Count({< 
        ACTION_CODE = {'COMPLETE'}, 
        WorkCenter = {'P_F01','P_F02'}, 
        DistinctSFCCount = {">=25"} 
    >} distinct SFC)
/ count (distinct GiornoSettimanaLavorato)

alespooletto_0-1736759638038.png

 

marcus_sommer

I think it's the second count which doesn't know any condition. You may try something like this:

Count({< 
        ACTION_CODE = {'COMPLETE'}, 
        WorkCenter = {'P_F01','P_F02'}, 
        DistinctSFCCount = {">=25"} 
    >} distinct SFC)
/ count({< DistinctSFCCount = {">=25"} 
    >}distinct GiornoSettimanaLavorato)
alespooletto
Creator II
Creator II
Author

Thank you. Well, this seems to have an effect, for sure, but there is still a day at 0 that gets counted in the overall. The interesting thing is that that 0 remains even if I filter for DistinctSFCCount > 1 manually.

alespooletto_1-1736761071996.png

 

 

marcus_sommer

It might be caused by the associations within the data-model and/or the object.

The object-check is quite simple and fast done by using the count({ Set } distinct Date) within a text-box which excludes all associations to other dimensions/expressions.

Checks and adjustments against the data-model may require more efforts. At first make sure that you didn't count against a KEY field and didn't use one as dimension in the object. Such approach will be working within the most scenarios but I noticed already "strange" effects if the relation-ship isn't 1:n and/or if there are missing key-values on any side. If there are any KEY fields involved you may just duplicate these fields and using them instead of the KEY fields to see if anything changed.   

alespooletto
Creator II
Creator II
Author

Thanks, I tried to run some checks. The model uses Date as a major connector, actually the model is really simple besides the tables above it just has a master calendar table and that's it. 

So in my case, do you know if there's a manual solution to avoid this issue? Maybe brute forcing the selection of only days where DistinctSFCCount is > 25?

EDIT: To be more precise, I think this could be to the fact that the ones that are still shown, are days which are within WeekDays 0 and WeekDays 4 (Mon - Fri). So the filter on dates works, but not for all of those with 0 SFCs made