topic Re: Expression error in QlikView

Expression error

Fri, 29 Jan 2016 16:40:14 GMT

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,

Re: Expression error

Peter_Cammaert — Fri, 29 Jan 2016 16:43:24 GMT

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

Re: Expression error

sunny_talwar — Fri, 29 Jan 2016 16:44:56 GMT

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.

Re: Expression error

Colin-Albert — Fri, 29 Jan 2016 16:47:04 GMT

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.

Re: Expression error

Peter_Cammaert — Fri, 29 Jan 2016 16:50:58 GMT

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];

Re: Expression error

sunny_talwar — Fri, 29 Jan 2016 16:53:36 GMT

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;

Re: Expression error

Fri, 29 Jan 2016 16:55:45 GMT

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

Regards,

Re: Expression error

Fri, 29 Jan 2016 17:56:51 GMT

You are a pro.Thank you

Re: Expression error

sunny_talwar — Fri, 29 Jan 2016 17:57:10 GMT

Re: Expression error

Fri, 29 Jan 2016 17:57:24 GMT

Thank you.That helped.