Processing on loading an Excel table in Qlik Sense

[Davy1]

Hello, I am working with Qlik Sense during my internship, and I am reaching out to you because I am stuck. That's why I am asking for your help and suggestions. I have an Excel table that contains the total order processing time in hours (this is the last relevant column), and I would like to write a script during loading that will remove the top 5% of rows with the highest processing times per month. To be more specific, I have a table that contains 100 orders for January and February 2023. After loading, the script should help me remove the 5 orders with the highest processing times for January and the same for February. In the result, I would like to have 95 remaining orders for January and 95 for February.

I used the rank function but didn't get any results. Thank you for your help.

[HirisH_V7]

Hi Do you want to rank the rows based on value or based on row load order ?

 Rank the rows based on value:

Autonumber(Value,Month) 

Based on row load order:

AutoNumber( Rowno(), Platform) as Rank

Use any of the above and restrict your number to load into.

 Else explain a bit more.

[marcus_sommer]

I think I would do several things:

• adding aggregated values to the table
• creating a ranking with interrecord-function
• evaluating aggregations + ranks against the row-values within n flags
• filtering the dataset against the flags - in the UI and only optional in the script

This may look like:

t1: load *, Month &'|'& Year & '|' & Document as KEY from X;

left join(t1)

load KEY,
        count(Hours) as a,
        min(Hours) as b,
        max(Hours) as c,
        avg(Hours) as d,
        median(Hours) as e,
        fractile(Hours, 0.05) as f,
        fractile(Hours, 0.95) as g
resident t1 group by KEY;

t2: load *, if(KEY = previous(KEY), peek('Rank') + 1, 1) as Rank
resident t1 order by KEY, Hours;

t3: load *, if(......) as Flag1, ....
resident t2:

drop tables t1, t2;