Group by & having more than one selected fields

[arezoo99]

Hi,

I have a group by query, i am getting Error 1 (below) since i am using the alias in group by for the if formula,,, what should i use in order not to get error,,,

even if i remove COS_GI_Status from my query i still get Error2,,,

my group by works if i have only one field in addition to the count field,,, if i select more fields i get error

Query:

load  CS_GEAR_ID,

      CS_Current_State as COS_Status,

      CS_Date as COS_Date,

      COUNT(CS_GEAR_ID)  as COS_Count,

      if(match(CS_Current_State,'New', 'Planned', 'Strategic Investment')>0, 'Total Live Apps','Total Decom/Withdrawn Apps') AS COS_GI_Status

Resident Current_Status

Group by COS_Current_State, COS_Date, COS_GI_Status;

Error 1:

Field not found - <COS_GI_Status>

Count_of_Status:

load  CS_GEAR_ID,

      CS_Current_State as COS_Status,

      CS_Date as COS_Date,

      COUNT(CS_GEAR_ID)  as COS_Count,

      if(match(CS_Current_State,'New', 'Planned', 'Strategic Investment')>0, 'Total Live Apps','Total Decom/Withdrawn Apps') AS COS_GI_Status

Resident Current_Status

Group by CS_Current_State, CS_Date, COS_GI_Status

Error 2:

Invalid expression

Count_of_Status:

load  CS_GEAR_ID,

      CS_Current_State as COS_Status,

      CS_Date as COS_Date,

      COUNT(CS_GEAR_ID)  as COS_Count

Resident Current_Status

Group by CS_Current_State, CS_Date

[captain89]

Hi,

you can't group by a new field. You must create it before.

I suggest you to load in qlik the fields and do the queries using a chart.

Anyway you can try this:

QueryW:

load  CS_GEAR_ID,

      CS_Current_State as COS_Status,

      CS_Date as COS_Date,

      if(match(CS_Current_State,'New', 'Planned', 'Strategic Investment')>0, 'Total Live Apps','Total Decom/Withdrawn Apps') AS COS_GI_Status

Resident Current_Status;

Count_of_Status:

load  COS_Status,

      COS_Date,

      COUNT(CS_GEAR_ID)  as COS_Count,

      COS_GI_Status

Resident QueryW

Group by

COS_Status, COS_Date, COS_GI_Status

drop table QueryW;

if you count(CS_GEAR_ID) it hasn't sense to group by CS_GEAR_ID so you can't put it in the load statement

[rwunderlich]

The calculated fields do not exist yet.  You can fix this by moving the Group By in a preceding load.

Query:

LOAD

     *

Group by COS_Current_State, COS_Date, COS_GI_Status;

load 

      CS_Current_State as COS_Status,

      CS_Date as COS_Date,

      COUNT(CS_GEAR_ID)  as COS_Count,

      if(match(CS_Current_State,'New', 'Planned', 'Strategic Investment')>0, 'Total Live Apps','Total Decom/Withdrawn Apps') AS COS_GI_Status

Resident Current_Status;

Note I also removed the CS_GEAR_ID from the load.  You can't keep that because you are aggregating  COUNT(CS_GEAR_ID).

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[arezoo99]

Thanks a lot,, it is working now

[arezoo99]

Thanks a lot,, it is working now

[arezoo99]

I have one more question:

do you know how I can label one of my fields with the value of another field

instead of count i want the date to be populated in the label,,, i can do it at the table level by using a parameter but i need to label this in the script not in the table

I have assigned the date value to a parameter in the script

set gear_date=COS_Date;

and then used the below in the label of that field in the table

=Date($(gear_date),'DD-MMM-YYYY')

[rwunderlich]

You need to get the value of COS_DATE in a variable, not the fieldname.  I'll assume there is only one value for COS_DATE.

LET gear_date=FieldValue('COS_Date', 1);

and then in your load

LOAD foo as [$(gear_date)], ....

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[arezoo99]

Thank you

how can i get this field added to the straight table because everyday after running the report the value of this parameter is getting changed and in the striaght table the value of the field is getting shown in dimension lists

[arezoo99]

I need to generate a table like below. it is an incremental load and will be run weekly. i have uploaded the initial data and saved in  QVD file. but i am strugling how i can load data from QVD file and append the new data to the old data from QVD in the table format below.

i have two issues:

1. how to lable the columns
2. when i am adding the new data to the old data, it is getting added to the rows not columns. i want new columns to get added to my straight table

Count_of_Status:
load  CS_Grouped_Status AS COS_Grouped_Status,
CS_Date as COS_Date,
COUNT(CS_ID)  as COS_Count
Resident Current_Status
Group by CS_Grouped_Status, CS_Date;

LoadQVD_AppsInGI:
LOAD COS_Grouped_Status as GI_Status,
COS_Date as GI_Date,
COS_Count AS GI_Count
from file.qvd (qvd);

// Trying append the new data with the old data available in QVD file

Incremental:
Load GI_Status as COS_Grouped_Status ,
GI_Date as COS_Date ,
GI_Count as COS_Count
Resident LoadQVD_AppsInGI;
  LEFT JOIN
  LOAD COS_Grouped_Status,
COS_Date,
COS_Count as [$(gear_date)]
Resident Count_of_Status;

Status	14-May	21-May	28-May
Total 'Live' Apps	32	33	38
Total Decom/Withdrawn Apps	4	3	2
Grand Total Apps	36	36	40

[arezoo99]

Hi Guys,

anyone can help me with my query

Thanks