How can i perform operation on the field which is created by "as" clause ?

[#1]

Hello Community ,

I need your help i want that How can i perform operation on the field which is created by "as" clause ?

The query is given below :

LOAD

    Slug,

    Block,

    hour(time#("Time",'hh:mm:ss:ff')) as [Extracted Hours],

    if([Extracted Hours]=0,Count([Extracted Hours])) as [00:00 - 01:00],

    if([Extracted Hours]=1,Count([Extracted Hours])) as [01:00 - 02:00],

    Count(Slug) as "Story Repeat"

FROM

Source File

Group By Slug,Block,"Extracted Hours";

In this query i created a new field named as "Extracted Hours" and i want to use the value of this field but i am getting error that "Extracted Hour" field not exist.

Kindly suggest me how could i use ?

Thanks in Advance.

Regards

[sravan_v007 · Accepted solution]

Hi,

There are 2 ways..

1. You have to take complete value what you mentioned before "as".

i.e. from above script

LOAD

    Slug,

    Block,

    hour(time#("Time",'hh:mm:ss:ff')) as [Extracted Hours],

    if(hour(time#("Time",'hh:mm:ss:ff'))=0,Count(hour(time#("Time",'hh:mm:ss:ff')))) as [00:00 - 01:00],

    if(hour(time#("Time",'hh:mm:ss:ff'))=1,Count(hour(time#("Time",'hh:mm:ss:ff')))) as [01:00 - 02:00],

    Count(Slug) as "Story Repeat"

FROM

Source File

Group By Slug,Block,Time;

2. Take a resident of the table and perform the calculations.

T1:

LOAD

    Slug,

    Block,

    hour(time#("Time",'hh:mm:ss:ff')) as [Extracted Hours],

Slug

FROM

Source File

T2:

LOAD

    Slug,

    Block,

    [Extracted Hours],

    if([Extracted Hours]=0,Count([Extracted Hours])) as [00:00 - 01:00],

    if([Extracted Hours]=1,Count([Extracted Hours])) as [01:00 - 02:00],

    Count(Slug) as "Story Repeat"

FROM

Source File

Group By Slug,Block,"Extracted Hours";

Regards,

Sravan.

[#3]

Hey Saravan its's working percfectly thanks a lot for the help ..

Regards

[#4]

This can also be done using a preceeding load.

LOAD

  *

    ,if([Extracted Hours]=0,Count([Extracted Hours])) as [00:00 - 01:00]

    ,if([Extracted Hours]=1,Count([Extracted Hours])) as [01:00 - 02:00]

;

LOAD

    Slug,

    Block,

    hour(time#("Time",'hh:mm:ss:ff')) as [Extracted Hours],

    Count(Slug) as "Story Repeat"

FROM

Source File

Group By Slug,Block,"Extracted Hours";