Script Error

[nikhilgarg]

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

[puttemans]

Hi Nikhil,

Let's try in a different way. I've recovered the script above, but reworked. The idea is basically to create first a variable that combines your dimensions you'd need, then count everything together based upon this variable, use this as a mapping, and bring it back to the original table.

Map_Item4:

MAPPING LOAD

     Dim1,

     Item4

FROM DEF;

Map_Item5:

MAPPING LOAD

     Dim1,

     Item5

FROM DEF.

Table1:

LOAD *,

          Applymap('Map_Item4', Dim1) as Item4,

          Applymap('Map_Item5', Dim1) as Item5;

LOAD Item1,

          Item2,

          Item3,

          Dim1

FROM XYZ;

Right Join (Table1)

LOAD Dim2,

          Dim3,

          Dim4,

          Item1

FROM ABC;

//In this point, you should have all your varaibles needed in Table 1

noconcatenate

Table2:

LOAD *,

     Dim1&'|'&Dim2&'|'&Dim3&'|'&Dim4 as Key

Resident Table1;

Drop Table 1;

Map_item4:

MAPPING LOAD

          Key,

          Count(Item4)

Resident Table2

Group By Key;

Table3:

     LOAD *,

          Applymap('Map_item4',Key) as [Item 4 Count]

RESIDENT Table2;

DROP Table2;

I hope this gives you what you need.

[nikhilgarg]

Hey,

It is giving me Count as 0. Also it is not loading data of table in which column is there on which we have to put count()

[puttemans]

Could you check then whether your Table 1 is properly formed? I.e. whether it contains all the fields you need? (comment all script after 'noconcatenate'.  I think there's something wrong in the joining then. I personally would rather map everything than join, but that's me.

[nikhilgarg]

Hey,

I checked after commenting code after noconcatenate, values of

LOAD Dim1,

          Item4, // On which count is to be made

          Item5

FROM DEF;

is not loading. It is showing no values

[sunny_talwar]

Can you share some sample data so that we can test run the script on our end?

I feel what we are giving you may not be incorrect, there might be something which we are missing.

Best,

S

[puttemans]

You're sure your DEF-file contains data right 🙂

Try with my adapted script (mapping) above please.

[nikhilgarg]

Hey,

i did the same way but it showing count as 0

[sunny_talwar]

Try this sample script:

Table1:

LOAD * INLINE [

    Item1, Item2, Item3, Dim1

    Car, a, e, AA

    Motorcycle, b, f, BB

    Motorboat, c, g, CC

    Airplane, d, h, DD

];

Right Join (Table1)

LOAD * INLINE [

    Dim2, Dim3, Dim4, Item1

    EE, II, MM, Car

    FF, JJ, NN, Motorcycle

    GG, KK, OO, Motorboat

    HH, LL, PP, Airplane

];

Left Join (Table1)

LOAD * INLINE [

    Dim1, Item4

    AA, Sun

    BB, Moon

    CC, Star

    DD, Galaxy

];

Join(Table1)

LOAD Dim1,

  Dim2,

  Dim3,

  Dim4,

  Count(Item4) as [Item 4 Count]

Resident Table1

Group By Dim1, Dim2, Dim3, Dim4;

output I get is:

Hope this helps.

Best,

S