topic How to Use Having Function in where Clause - Tricky in QlikView

How to Use Having Function in where Clause - Tricky

Mon, 07 Feb 2011 08:28:39 GMT

Hi all,

I need your help & guidance in this 🙂

How do we use Having function where i would like to use count(plantcode) >1 etc..

I have attached a sample excel file which explains my Requirement.

1st Sheet is the raw data to be imported into Qv & the 2nd sheet explains the output required by me.

Can you all guys throw some light on this & will be of gr8 help 🙂

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/63623.Help-Report.xls:550:0]

Srini

Mon, 07 Feb 2011 09:58:58 GMT

Hi All,

I have done but want you all to please share your though process to make the code better

any other ideas 🙂

LOAD

Date as Key,

// date(Date,'DD/MM/YYYY')&'-'&Plant as Key,

Plant,

Product,

Date,

Vendor,

Amt

FROM

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

left Join(A)

LOAD

Date as Key,

if(count(Plant) > 1,date(Date,'DD/MM/YYYY')) as NewDate,

//Plant as PlantNew,

sum(Amt) as Amt1

Resident A

Group By Date

Order By Date asc;

LOAD *,

'' as junk

Resident A

where Date = NewDate;

DROP Table A;

DROP Field junk;