topic Re: Resident Syntax in QlikView

Resident Syntax

Tue, 04 Aug 2015 06:53:54 GMT

Hi All,

IN the below Syntax i am using Resident Table for Grouping.

And if you look at the Productivity Table & Resident Table New , ID is used as Key.Iinstead of it I need to use the actual Key field in Productivity table as Key for both the tables so that when i select date the average Bucket is calculated for the selected dates .In the below script irrespective of date selected the average is calculated for all dates.

MTS:

MAPPING LOAD [Opr Initials],

FROM

(biff, embedded labels, table is [Sheet2$]);

Tab:

LOAD [@1:4] as Interval1,

[@5:12] as ID1,

[@13:18] as [Dept ID],

[@19:30] as Status,

[@31:41] as Handled,

[@42:n] as Closed,

Subfield(FileName(),'.',1) AS Date_Test

FROM

(fix, codepage is 1252);

NoConcatenate

Productivity:

LOAD Date(Date#(Date_Test,'YYYY-MM-DD'),'YYYY-MM-DD') As Date,

if(len(trim(Interval1))=0,peek('Interval'),Interval1) AS Interval,

if(len(trim(ID1))=0,peek('ID'),ID1) AS ID,

'SCSTAR' as Source,

if(len(trim(ID1))=0,peek('ID'),ID1) & Date(Date#(Date_Test,'YYYY-MM-DD'),'YYYY-MM-DD') as Key,

Resident Tab Where len(Trim([Dept ID]))>0;

DROP Table Tab;

DROP Fields Interval1,ID1,Date_Test;

Concatenate

LOAD Date,

[Opr Initials],

ApplyMap('MTS',[Opr Initials]) as ID,

OFP,

OFA,

OFP+OFA as Handled,

'JPN' as [Dept ID],

'MTS-JPN' as Source,

ApplyMap('MTS',[Opr Initials]) & Date as Key,

Memo

FROM

(ooxml, embedded labels, table is List_Frame_1);

Concatenate

LOAD [Maker BankID] as ID,

Date(Date#([Date (DD/MM/YYYY)],'DD/MM/YYYY'),'YYYY-MM-DD')as Date,

Volume as Handled,

'FFT' as [Dept ID],

'MTS-FFT' as Source,

[Maker BankID] & Date(Date#([Date (DD/MM/YYYY)],'DD/MM/YYYY'),'YYYY-MM-DD')as Key,

[Role (50)]

FROM

(ooxml, embedded labels, table is Sheet1);

New:

LOAD

ID,

//Sum(Handled)/Count(Distinct(Date)) as AvgHandled,

if(Sum(Handled)/Count(Distinct(Date)) < 400, '0-400',

if(Sum(Handled)/Count(Distinct(Date)) < 800, '400-800',

if(Sum(Handled)/Count(Distinct(Date)) < 1000, '800-1000',

if(Sum(Handled)/Count(Distinct(Date)) < 1500, '1000-1500', '> 1500')))) as Average

Resident Productivity

Group by ID;

How do i link dates between both Productivity & New table

Attaching teh QVW

Re: Resident Syntax

Anonymous — Tue, 04 Aug 2015 08:41:45 GMT

hi,

use interval function for creating buckets for calculating avg for specific date ranges.

Regards

Neetha

Re: Resident Syntax

jonathandienst — Tue, 04 Aug 2015 08:42:55 GMT

Because the table (New) with the average values is associated only by ID, so the average will be for possible values ID, regardless of date.

If you want user selectable date ranges, then the Average should be calculated in the front end expressions and not in the script.

Re: Resident Syntax

Gabriel — Tue, 04 Aug 2015 08:43:55 GMT

Hi,

Am not sure I fully understand your point. But I think you can bring in the Date field from Productivity Table since you're using Resident to create the New table.

So just add Date field into the New table and that should work

Re: Resident Syntax

Tue, 04 Aug 2015 08:43:58 GMT

I am really new to Qlikview.Can you help me out in writing teh syntax

Re: Resident Syntax

Anonymous — Tue, 04 Aug 2015 08:48:44 GMT

please can you give requirements (date ranges) to be calculated for avg in script

Re: Resident Syntax

Tue, 04 Aug 2015 08:48:45 GMT

If i write it this way

New:

LOAD

ID , Date,

if(Sum(Handled)/Count(Distinct(Date)) < 400, '0-400',

if(Sum(Handled)/Count(Distinct(Date)) < 800, '400-800',

if(Sum(Handled)/Count(Distinct(Date)) < 1000, '800-1000',

if(Sum(Handled)/Count(Distinct(Date)) < 1500, '1000-1500', '> 1500')))) as Average

Resident Productivity

Group BY ID;

It says Invalid exopression and more over it will create Sysnthetic Key as i have both ID & Date so if change ID as ID1 then group by Doesnot work .

And if i change Group by ID1; then it throws as error saying Field ID1 doesnot exist

Re: Resident Syntax

Anonymous — Tue, 04 Aug 2015 08:50:24 GMT

Include date in group by or exclude date in load statement for group by

Re: Resident Syntax

Tue, 04 Aug 2015 08:50:28 GMT

I am able to get average outside scrip in graph but if i want to bucklt each agent under a varios bucklets to see the locationwise Count under each bucket then i need to do it Script only right.Is

Re: Resident Syntax

Tue, 04 Aug 2015 08:53:32 GMT

i cannot add date in group by as I wanted the Average for the entire period and if i add date in group it stars calculating teh avg for each dates and creates bucket so finally one officer will have many buckets

Re: Resident Syntax

Tue, 04 Aug 2015 08:54:07 GMT

Just try in teh QVW which i have attached to see if it works at your end

Re: Resident Syntax

Anonymous — Tue, 04 Aug 2015 09:02:22 GMT

Hi,

Please be specific what you want to achieve.

so can help

Re: Resident Syntax

Anonymous — Tue, 04 Aug 2015 09:06:26 GMT

Use Aggr function in expression in UI to group by ID

Re: Resident Syntax

Tue, 04 Aug 2015 09:09:56 GMT

I wanted to get output like this.

Location	Average	0-400	400-800	800-1000	1000-1500
Bangalore		10	6	4	1
Haddows		15	6	-	-
Haddows - TCS		8	-	-	-
Kenya		3	1	-	-
KL		3	1	-	-
Padur		1	2	2	-
Tianjin		13	3	-	-