topic Re: Processing on loading an Excel table in Qlik Sense in Qlik Automate

Processing on loading an Excel table in Qlik Sense

Davy1 — Mon, 24 Jun 2024 15:17:16 GMT

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.

Re: Processing on loading an Excel table in Qlik Sense

HirisH_V7 — Tue, 25 Jun 2024 06:17:13 GMT

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.

Re: Processing on loading an Excel table in Qlik Sense

marcus_sommer — Tue, 25 Jun 2024 06:22:59 GMT

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;