Expression error

[#1]

Test:

LOAD

     Address,

     City,

     [Annual Rent],

     [Providers - Physician],

     [providers - Physcian Assistant],

      Nurse,

      HeadCount,

      Capacity

FROM

(ooxml, embedded labels, table is Locations);

FinalTABLE:

lOAD *,

SUM([Providers - Physician]) AS value,

[Providers - Physician] as provider

Resident Test

group by [Providers - Physician];

Concatenate(FinalTABLE)

lOAD *,

SUM([providers - Physcian Assistant]) AS value,

[roviders - Physcian Assistant] as provider

Resident Test

group by [roviders - Physcian Assistant];

Concatenate(FinalTABLE)

lOAD *,

SUM([Nurse]) AS value,

[Nurse] as provider

Resident Test

group by Nurse;

drop Table Test;

I have the aboce script in Script editor,but it says expression error for some reason.Can somebody help me with that.

Thanks,

[Peter_Cammaert]

It would help if you could specify where exactly it says "expression error". Statements are executed one after the other.

Making a guess: individual fields in a GROUP BY load should either appear as parameter of an aggregation funciton, or in the GROUP BY list. You're doing a LOAD * which is illegal.

Peter

[sunny_talwar]

I think the issue is here:

FinalTABLE:

lOAD *,

SUM([Providers - Physician]) AS value,

[Providers - Physician] as provider

Resident Test

group by [Providers - Physician];

Concatenate(FinalTABLE)

lOAD *,

SUM([providers - Physcian Assistant]) AS value,

[roviders - Physcian Assistant] as provider

Resident Test

group by [roviders - Physcian Assistant];

Concatenate(FinalTABLE)

lOAD *,

SUM([Nurse]) AS value,

[Nurse] as provider

Resident Test

group by Nurse;

drop Table Test;

You are grouping by the fields which are aggregated, whereas you need to group by those fields which are not aggregated.

[Colin-Albert]

In the script editor, you can select Debug and then Step to execute the script row by row on each click of Step or select Animate which runs the script slowly and indicates the row being processed.

This will identify which command is causing the error.

[Peter_Cammaert]

Or even by fields that don't exist at all. From your example (and the script code):

:

[roviders - Physcian Assistant] as provider

Resident Test

group by [roviders - Physcian Assistant];

[sunny_talwar · Accepted solution]

Can you give this a short:

Test:

LOAD

     Address,

     City,

     [Annual Rent],

     [Providers - Physician],

     [providers - Physcian Assistant],

      Nurse,

      HeadCount,

      Capacity

FROM

(ooxml, embedded labels, table is Locations);

FinalTABLE:

LOAD Address,

     City,

     [Annual Rent],

     [Providers - Physician] as provider,

     HeadCount,

     Capacity,

     Sum([Providers - Physician]) as value

Resident Test

Group By Address, City, [Annual Rent], [Providers - Physician], HeadCount, Capacity;

Concatenate(FinalTABLE)

LOAD Address,

     City,

     [Annual Rent],

     [providers - Physcian Assistant] as provider,

     HeadCount,

     Capacity,

     Sum([providers - Physcian Assistant]) as value

Resident Test

Group By Address, City, [Annual Rent], [providers - Physcian Assistant], HeadCount, Capacity;

Concatenate(FinalTABLE)

LOAD Address,

     City,

     [Annual Rent],

     [Nurse] as provider,

     HeadCount,

     Capacity,

     Sum([Nurse]) as value,

Resident Test

Group By Address, City, [Annual Rent], Nurse, HeadCount, Capacity;

DROP Table Test;

[#7]

You need to group by all the fields of the Test table.

Regards,

[#8]

You are a pro.Thank you

[sunny_talwar]

[#10]

Thank you.That helped.