group by: need a Timestamp in a group by statement?

[chriys1337]

Hi,

I have a question regarding my group by statement.

I am loading following table with a Time_Stamp, which is generated with num(today()). So for every row there will be a identical number from today as Time_Stamp, e.g. "42990" for 12.09.2017.

Do I really need to put num(today()) in the group by? I have tried it without and it seems to work, but I remember the rule for group by was to put all fields in the group by which are not summed.

Can anybody please explain? Thx Chris

Load sum(Rev) as Rev,

          SoS_Year, //1

          Geo_From_Subregion_Group_Name, //2

          Geo_To_Subregion_Group_Name,  //3

          num(today()) as Time_Stamp //4

Resident fact_table 

group by SoS_Year,

Geo_From_Subregion_Group_Name,

Geo_To_Subregion_Group_Name

//     ,num(today())

;

[sunny_talwar]

Add Time_Stamp field in the Fact_table and then bring it in this aggregated table as a field

fact_table:

LOAD SoS_Year,

     Geo_From_Subregion_Group_Name,

     Geo_To_Subregion_Group_Name,

     num(today()) as Time_Stamp,

     ....

     ...

FROM ....;

Load sum(Rev) as Rev,

          SoS_Year, //1

          Geo_From_Subregion_Group_Name, //2

          Geo_To_Subregion_Group_Name,  //3

          Time_Stamp //4

Resident fact_table

group by SoS_Year,

Geo_From_Subregion_Group_Name,

Geo_To_Subregion_Group_Name,

Time_Stamp

;

[Kushal_Chawda]

try this

LOAD *,

           num(today()) as Time_Stamp;

Load sum(Rev) as Rev,

          SoS_Year, //1

          Geo_From_Subregion_Group_Name, //2

          Geo_To_Subregion_Group_Name,  //3

Resident fact_table

group by SoS_Year,

Geo_From_Subregion_Group_Name,

Geo_To_Subregion_Group_Name;

[chriys1337]

Thank you Sunny and Kushal, both are valid ways, the resident and the preceding load. My topic is that,my original table (including the time_stamp) with the group (without the time_stamp) is working fine as well, although I ignored the rule to put the time_stamp in the group by statement .

So is this a valid way, or should I better stick to the resident or preceding load which you have presented? A small explanation would be great, thx a lot.

[Kushal_Chawda]

Yes of course it is valid way and also better way too because you are keeping only one load statement instead taking resident

[Brice-SACCUCCI]

Hello,

You would have to add the field in your Group By... if it was a field from the table you are reading.

In your case, you are creating this field from scratch, without using the columns from fact_table.

I hope it's clearer now

Regards,

Brice