Fetch Data according to sorting

[berryandcherry6]

Hi,

I have below Script

HelpRequetData:

LOAD `ticket_id`,

`user_id`  ,

    `date_created` as Help_request_Date;

SQL SELECT `ticket_id`,

    `user_id`,

    `date_created`

FROM HELPREQUESTS WHERE date_created >= '$(vWeekStart)' and date_created <= '$(vWeekEnd)' ;

Left Join(HelpRequetData)

LOAD

`user_id` ,

    `client_id`;

SQL SELECT `user_id`,

    `client_id`

FROM USERS;

LOAD  `client_id`,

    `client_name`;

SQL SELECT `client_id`,

    `client_name`

FROM CLIENT ;

here i need to fetch data like it has to be sorted in client Name Wise alphabetically. As shown below

user_id   client_id   client_name

2              1              Abacus

4               3             matie

5              5              sjila

6              4              jaimon

710           2             oreal

34            1              Abacus

678            3             matie

For above i need it to show like below

user_id   client_id   client_name

2              1              Abacus

34            1              Abacus

6              4              jaimon

4               3             matie

678            3             matie

710           2             oreal

5              5              sjila

so that when where i retrieve user_id   it has to be order by client_name wise.

How could i sort it? Please help on this.

[ganeshreddy]

Hi Supriya,

Try by adding below script,

Noconcatinate

tmp_HelpRequetData: 

Load

user_id ,

client_id   ,

client_name  

Resident HelpRequetData

order by client_name asc ;

Drop table HelpRequetData;

Rename table tmp_HelpRequetData to HelpRequetData;

Thanks,

Ganesh

[berryandcherry6]

Hi, thanks for reply.

But client_name is present in CLIENT Table right?. How could i put two table fields in one load?

It gives error syaing

The following error occurred:

Field 'client_name' not found

[vinieme12]

Hi

Please post the complete script where you load CLIENT table. also post complete error message.

The Client_Name field is relevant to CLIENT table so looking at load scripts of other tables is irrelevant.

For Fixing the sort Order refer the attached app

temp:

Load * Inline [

user_id,client_id,client_name

2,1,Abacus

4,3,matie

5,5,sjila

6,4,jaimon

710,2,oreal

34,1,Abacus

678,3,matie];

NoConcatenate

FACT:

LOAD * , RowNo() as SortORder

Resident temp

Order by client_name;

drop table temp;

Now Use Sort by Expression >> SortOrder

[berryandcherry6]

Hi,

See my original post. It has three tables with a left join and then i have to ordering  according to client in script itself.

I should not use sort expression.

How could i join tables with ordering. so i get user id according to client name order.

Please help me on this.

[vinieme12]

temp_HelpRequetData:

LOAD `ticket_id`,

`user_id`  ,

    `date_created` as Help_request_Date;

SQL SELECT `ticket_id`,

    `user_id`,

    `date_created`

FROM HELPREQUESTS WHERE date_created >= '$(vWeekStart)' and date_created <= '$(vWeekEnd)' ;

Left Join(temp_HelpRequetData)

LOAD

`user_id` ,

    `client_id`;

SQL SELECT `user_id`,

    `client_id`

FROM USERS;

Left Join(temp_HelpRequetData)

LOAD  `client_id`,

    `client_name`;

SQL SELECT `client_id`,

    `client_name`

FROM CLIENT ;

HelpRequetData

Load  * ,Rowno() as SortOrder

RESIDENT temp_HelpRequetData

Order by client_name,Help_request_Date;

Drop Table temp_HelpRequetData;

[berryandcherry6]

Hi,

In HELPREQUESTS table i dont have client_id or client_name. So i cant join CLIENT table with HELPREQUESTS Table.

[vinieme12 · Accepted solution]

Supriya,

We are joining the data  like this aren't we?

table1:

temp_HelpRequetData has fields , ticket_id,user_id,date_created     

table2: user_id,client_id

table3:  client_id,client_name   

between table1 and table2 we have user_id as Key  and between table 2 and table 2 client_id as Key

so finally we have one big table

temp_HelpRequetData with all the fields that we need

ticket_id,user_id,date_created ,client_id,client_name

Can you try the script I posted??

[berryandcherry6]

Hi Vineeth,

Thanks, issue was i had changed sorting order to numerically. After removing it it works fine.