Aggr and Rank in Load Script

[Anonymous]

Hello everyone,

I have a problem where i need to convert these code into a readable code in the load script.

If( [Total Complaint Received] = 0, 1, Aggr(Rank(-Sum([Total Complaint Received]), 1,1), Factory) - 1)

As we all know, both Aggr and Rank function cannot use in the load script. Does anyone know how?

Thank you in advance!!!

[OmarBenSalem]

for rank, u can use   AutoNumber(VALUE) as Rank

and order your table the way u want to rank it (using Order By clause)

Then, for Aggr, u replace it by GROUP BY clause.

[OmarBenSalem]

U can try to refer to this:

Rank Function load script

[ksomosera10]

Hi Muhammad Hazim,

Good day.

You can still use the functionality of AGGR on the Load Script.

Just use GROUP BY instead.

For example:

LOAD

    * ,

    If([Total Complaint Received]) = 0, 1) as [NewField]

FROM (File)

GROUP BY [Factory];

Thanks!

[Anonymous]

Thank you for your info!

[Anonymous]

Hi Kevin,

I used your codes but then it shows this error. How to fix this??

Thank you in advance!

[ksomosera10]

Hi,

By using 'Group By' you should include other fields except the [Rank]

Because I have no idea about the fields that you have, I just used the one field.

I hope it makes sense.

If you still encounter an error just beep me up.

Thanks!

[OmarBenSalem]

to use Group by in the script;

u should use an aggr function (sum / count, only etc) and group ur calculation by all the other dimensions.

Example:

u have this table:

ID	AGE	SALES
1	20	10
2	20	10
2	20	10
2	20	10
3	25	10
4	30	10
4	30	10
4	30	10

If u want just a new table as follow:

aggr(Sum(Sales),ID)

U should do this:

load ID,

Sum(Sales) as SalesByID

Resident urTable

group by ID;

Result:

ID	SalesByID
1	10
2	30
3	10
4	30

if u want aggr(sum(Sales),Age)

U should do this:

load Age,

Sum(Sales) as SalesByAge

Resident urTable

group by Age;

Result:

Age	SalesByAge
20	40
25	10
30	30

Now if u want sales by Age then ID

aggr(Sum(Sales),Age, ID)

u should do :

load ID,

Age,

Sum(Sales) as SalesByAgethenID

resident urTable

Group By Age,ID;

Result

Age	ID	SalesByAgethenID
20	1	10
20	2	30
25	3	10
30	4	30

=> You should always group by all the other dimensions in ur table; and deopends on what u want, make sure to group in the right order.

The first dimension would be the first grouping and so on

Hope that was clear and helps

[Anonymous]

Thank you for your explanation!

Im still green on this, never understand aggr function clearly.

But you help me to clarify.

Thank You Again!

[ksomosera10]

Good to hear!

Just ask if you need help^^