Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'));
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'));
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.
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.
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)
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)
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)
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.
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.
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