topic Re: Script Error in QlikView

Script Error

nikhilgarg — Wed, 04 Mar 2015 12:33:49 GMT

HEy,

I am using following script but it is giving me error as Invalid Expression:

Table3:

LOAD

Item1,

Item2,

Item3,

Count(Item4) as #Item

Resident Table1 Group By Region;

What is the issue??

tHanks

Re: Script Error

sunny_talwar — Wed, 04 Mar 2015 12:40:33 GMT

Try and see if this works

Table3:

LOAD

Region,

Count(Item4) as #Item

Resident Table1

Group By Region;

Best,

Re: Script Error

puttemans — Wed, 04 Mar 2015 12:41:05 GMT

Hi,

I think you need to instruct more on the grouping, and you need to add Region in the variable list.

So it would look like :

Table3:

Item1,

Item2,

Item3,

Count(Item4) as #Item,

Region
Resident Table 1

Group by Region, Item1, Item2, Item3;

You now ask to group on a variable that is not there, and if it's there, then QV still has too many variables open.

Re: Script Error

Wed, 04 Mar 2015 12:42:55 GMT

1. You do not have Region in your table

2. Aggration will only work if you have all items some thing like

Table3:

LOAD

Item1,

Item2,

Item3,

Count(Item4) as #Item

Resident Table1

Group By

Item1,

Item2,

Item3

;

Re: Script Error

nikhilgarg — Wed, 04 Mar 2015 12:48:47 GMT

It is giving me Syntax Error

Re: Script Error

puttemans — Wed, 04 Mar 2015 12:51:13 GMT

can you post your syntax?

Re: Script Error

sunny_talwar — Wed, 04 Mar 2015 12:52:00 GMT

What exactly are you trying to accomplish?? It would be easier if we know your ultimate goal.

Best,

Re: Script Error

nikhilgarg — Wed, 04 Mar 2015 13:03:40 GMT

Well,

I have a pivot table with 4 dimensions. I added a expr to count values of a other dim.

Now , i ahve to cross check dat the count in pivot tables should be same when directly fetching from sql.

So, in that case i have to put GroupBy clause on every Dim used in pivot, ryt?

Table1:

Dim1 , dim2 ,dim3 are in one table and dim4 in other table.

So, i load dim1 , dim2 , dim3 from one table and right join Load dim4 from other table.

LeftJoin:

Now i have loaded Dim5 on which i have to put count functIOn.

Now i ahve Complete Table1. I used Resident table function as:

Table3:

LOAD

Dim1, Dim2, Dim3, Dim4 , Count(Dim5) as #Item Resident Table1 Group By Dim1, Dim2 , Dim3 , Dim4

But i am having Syntax Error

Re: Script Error

sunny_talwar — Wed, 04 Mar 2015 13:09:12 GMT

Does your Table1 with dim1, dim2 & dim3 have anything common with your second table which contains dim4 (Does it have dim1, dim2 or dim3) The reason I ask this is because if nothing is common then your right join will completely empty your dim1, dim2 & dim3.

You will need to give us more than this. Can you paste your actually script?

Best,

Re: Script Error

renjithpl — Wed, 04 Mar 2015 13:18:00 GMT

May be this solves,

Table_New:

Noconcatenate LOAD

Dim1, Dim2, Dim3, Dim4, Count(Dim5) as #Item

Resident Table1 Group By Dim1, Dim2 , Dim3 , Dim4;

Drop table Table1;

Thanks

Ren

Re: Script Error

nikhilgarg — Wed, 04 Mar 2015 13:19:07 GMT

Hey,

I cann't paste script bcoz my project is on VDI. ANyways, will this help u??

Table1:

LOAD

Item1,

Item2,

Item3,

Dim1;

RightJoin(Table1)

Load

Dim2,

Dim3,

Dim4,

Item1;

Now i ahve to load dat data on which count is to be made:

Load

Dim1,

Item 4, // On which count is to be made

Item5;

Now, I want to Count(Item4) making GroupBy clause with Dim1 ,Dim2, Dim3 , Dim4.

How can i do this??

Re: Script Error

sunny_talwar — Wed, 04 Mar 2015 13:28:44 GMT

Hey Nikhil your right join is not going to be very helpful bro. You have no common fields between the two.

Table1:

LOAD

Item1,

Item2,

Item3,

Dim1;

RightJoin(Table1)

Load

Dim2,

Dim3,

Dim4,

Item1;

Guys, correct me if I am wrong, but for his right join to work the way I am hoping he want it to work, he would need to have something in common, right?

Best,

Re: Script Error

nikhilgarg — Wed, 04 Mar 2015 13:35:43 GMT

Hey,

In both he tables, Item1(column name) is common.

Re: Script Error

sunny_talwar — Wed, 04 Mar 2015 13:38:12 GMT

My bad, you are right.

Re: Script Error

nikhilgarg — Wed, 04 Mar 2015 13:39:34 GMT

Hey,

But this will create a synthetic key , isn't it

Re: Script Error

nikhilgarg — Wed, 04 Mar 2015 13:40:08 GMT

Ohk then plz guide me how can i acheive my goal from last reply

Re: Script Error

sunny_talwar — Wed, 04 Mar 2015 13:41:41 GMT

Try this:

Table1:

LOAD Item1,

Item2,

Item3,

Dim1

FROM XYZ;

Right Join (Table1)

LOAD Dim2,

Dim3,

Dim4,

Item1

FROM ABC;

Left Join (Table1)

LOAD Dim1,

Item4, // On which count is to be made

Item5

FROM DEF;

Join (Table1)

LOAD Dim1,

Dim2,

Dim3,

Dim4,

Count(Item4) as [Item 4 Count]

Resident Table1

Group By Dim1, Dim2, Dim3, Dim4;

Best,

Re: Script Error

nikhilgarg — Wed, 04 Mar 2015 13:49:48 GMT

All column values for Table which has column(on which count is to be done) is not coming

It is giving Count(Item4) as 0

And why do we use Join(Table1) at last ??

Re: Script Error

sunny_talwar — Wed, 04 Mar 2015 13:55:36 GMT

So that you don't have a synthetic key in your data structure. You just responded to Renjith Pl, that you wouldn't want to see a synthetic key. So I wondered it would be ideal if we join the whole thing back to the original table.

Best,

Re: Script Error

nikhilgarg — Wed, 04 Mar 2015 14:16:26 GMT

I didn't get you. DO you have any idea how to acheive what i want??

tHanks