topic Re: group by: need a Timestamp in a group by statement? in QlikView

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

chriys1337 — Wed, 13 Sep 2017 11:23:28 GMT

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())

;

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

sunny_talwar — Wed, 13 Sep 2017 11:27:04 GMT

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

;

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

Kushal_Chawda — Wed, 13 Sep 2017 11:32:38 GMT

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;

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

chriys1337 — Wed, 13 Sep 2017 12:05:04 GMT

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.

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

Kushal_Chawda — Wed, 13 Sep 2017 12:12:02 GMT

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

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

Brice-SACCUCCI — Wed, 13 Sep 2017 12:16:32 GMT

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