Script error

[nareshthavidishetty]

Hi,

I have used the below query in script level but it is giving the below error.

Logic : concat(A7BANB,',') as new,

[sunny_talwar]

You forgot to use Group By statement at the end

[Chanty4u]

Concat  function required group by clause  I think.

May  be you required a group by clause at the end of your script

like

group by  Category1...

[tyagishaila]

You are missing group by function.

[nareshthavidishetty]

Hi ,

I have used the below script but still i'm not able to merge the invoice numbers into one row.

Requirement: In the below image for salesman Balka,Damion the invoice # is showing as

30777459

30777609

30778676

30778882

30780296

Here ineed to show them in single row as 30777459,30777609,30778676,30778882,30780296

Image:

Script:

LOAD

     'Sign' as flag,

      A7AJCD,

      A7AACD as Loc,

     A7AIDT as Date,

     ESIGN_COMPLETE_NEW as Sales,

     'Complete'&'-'&SIGN_COMPLETE_NEW as Category1,

     Concat(A7BANB, ', ') as NEW,

     A7BANB,

     APA0TX

FROM

(qvd)Group By A7AACD,A7AIDT,ESIGN_COMPLETE_NEW,A7BANB,APA0TX,A7AJCD;

Thanks..

[sunny_talwar]

Which field is location and which one is salesman in the above script?

[sdmech81]

in group by u have missed,

SIGN_COMPLETE_NEW

plss add that n try

Sachin

[nareshthavidishetty]

Hi ,

Please find below.

Location : A7AACD as Loc

SalesMan : APA0TX

Invoice # : Concat(A7BANB, ', ') as NEW

Sales : ESIGN_COMPLETE_NEW as Sales

Thanks..

[sunny_talwar · Accepted solution]

Try this:

Script:

LOAD

     'Sign' as flag,

      A7AJCD,

      A7AACD as Loc,

     A7AIDT as Date,

     ESIGN_COMPLETE_NEW as Sales,

     'Complete'&'-'&SIGN_COMPLETE_NEW as Category1,

     A7BANB,

     APA0TX

FROM

(qvd);

Left Join (Script)

LOAD A7AACD,

           APA0TX,

          Concat(A7BANB, ', ') as NEW

Resident Script

Group By A7AACD, APA0TX;

[nareshthavidishetty]

Hi,

Now am able to get all those numbers into one row.

Here i need to get those sales count also needs to add.Here i need to get total as 3 with in single cell.

Color metric calculation(dimension):

=aggr(num((sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales))

/(sum({<Date={$(varMaxDate)},flag={'Wet Sign'}>} Sales)+sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Complete'}>} Sales)

+sum({<Date={$(varMaxDate)},flag={'eSign'},Category={'Not Complete'}>} Sales)),'

#,##0.0%'),Loc,APA0TX,A7BANB)

Expression:

sum({<Date={$(varMaxDate)}>} Sales)

Thanks..