topic Re: Group by & having more than one selected fields in QlikView

Group by & having more than one selected fields

arezoo99 — Tue, 25 Sep 2018 08:20:39 GMT

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

Re: Group by & having more than one selected fields

captain89 — Tue, 25 Sep 2018 17:10:25 GMT

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

Re: Group by & having more than one selected fields

rwunderlich — Tue, 25 Sep 2018 17:39:25 GMT

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

Re: Group by & having more than one selected fields

arezoo99 — Wed, 26 Sep 2018 06:35:37 GMT

Thanks a lot,, it is working now

Re: Group by & having more than one selected fields

arezoo99 — Wed, 26 Sep 2018 06:35:59 GMT

Thanks a lot,, it is working now

Re: Group by & having more than one selected fields

arezoo99 — Wed, 26 Sep 2018 06:43:52 GMT

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

Re: Group by & having more than one selected fields

rwunderlich — Wed, 26 Sep 2018 15:43:41 GMT

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

Re: Group by & having more than one selected fields

arezoo99 — Thu, 04 Oct 2018 06:47:07 GMT

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

Re: Group by & having more than one selected fields

arezoo99 — Thu, 04 Oct 2018 08:22:19 GMT

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:

how to lable the columns
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

Re: Group by & having more than one selected fields

arezoo99 — Sun, 14 Oct 2018 08:25:38 GMT

Hi Guys,

anyone can help me with my query

Thanks

Re: Group by & having more than one selected fields

rwunderlich — Mon, 15 Oct 2018 18:46:36 GMT

You should start a new thread on this question as it is a new question.

-Rob