topic Expression error in QlikView

Expression error

nareshthavidishetty — Mon, 21 Sep 2015 13:25:50 GMT

Hi,

I need to find the error in expression like i am getting red underline at the flower braces,can any one find out why it is happen and please guide me.

Re: Expression error

sunny_talwar — Mon, 21 Sep 2015 13:29:28 GMT

Can you post this expression as text instead of image? Also, you can try using If statement within Count instead of Set Analysis or create a field in the script for the left hand side of the set analysis equation.

Re: Expression error

agni_gold — Mon, 21 Sep 2015 13:30:44 GMT

Please copy paste your script

Re: Expression error

nareshthavidishetty — Mon, 21 Sep 2015 13:45:27 GMT

if (CenterName='ThirdParty',0,1-((count({<vDate={">=$(=(MonthStart(vYesterday)))<=$(=vYesterday)"}>} if((Satisfied<4.5),ServiceOrderNo))

)/(count({<vda={">=$(=(MonthStart(vYesterday)))<=$(=vYesterday)"}>) ServiceOrderNo)))

Re: Expression error

nareshthavidishetty — Mon, 21 Sep 2015 13:45:51 GMT

if (CenterName='ThirdParty',0,1-((count({<vDate={">=$(=(MonthStart(vYesterday)))<=$(=vYesterday)"}>} if((Satisfied<4.5),ServiceOrderNo))

)/(count({<vda={">=$(=(MonthStart(vYesterday)))<=$(=vYesterday)"}>) ServiceOrderNo)))

Re: Expression error

nareshthavidishetty — Mon, 21 Sep 2015 13:47:00 GMT

am getting the error at vDate={ at this brace

Re: Expression error

sunny_talwar — Mon, 21 Sep 2015 13:53:07 GMT

vDate is a variable or a field created in script?

Re: Expression error

agni_gold — Mon, 21 Sep 2015 14:23:34 GMT

Sunny is right , please explain some more about your expression

Sunny T

Re: Expression error

nareshthavidishetty — Mon, 21 Sep 2015 14:54:33 GMT

It's a variable like vDate=date#(%key,'0123456789','MM/DD/YYYY) WHERE %KEY IS A COMPOSITE KEY

Re: Expression error

sunny_talwar — Mon, 21 Sep 2015 15:00:30 GMT

I think you should create a field called date in your Script:

LOAD %key,

Date(Date#(KeepChar(%key,'0123456789/'),'MM/DD/YYYY)) as DateField

From Source

Then give a shot at your expression with the following changes:

If(CenterName='ThirdParty', 0, 1-((Count({<DateField={">=$(=Date(MonthStart(vYesterday)))<=$(=Date(vYesterday))"}>} if((Satisfied<4.5),ServiceOrderNo))

)/(count({<DateField={">=$(=Date(MonthStart(vYesterday)))<=$(=Date(vYesterday))"}>) ServiceOrderNo)))

Re: Expression error

nareshthavidishetty — Mon, 21 Sep 2015 15:32:27 GMT

Here %key=centername&'_'&Enterdate but to avoid syntetickeys I drop the field enterdate..finally %key contains value as 10/12/2015_centername

Re: Expression error

nareshthavidishetty — Mon, 21 Sep 2015 15:35:31 GMT

In front end I create a variable like vdate=date#(%key,'0123456789/'),MM/DD/YYYY)

Re: Expression error

nareshthavidishetty — Mon, 21 Sep 2015 16:19:38 GMT

Hi Sunny,

As i have enterdate in every table tha'ts the reason i created composite key on top these two fields,please analyze in this way

Re: Expression error

sunny_talwar — Mon, 21 Sep 2015 17:48:05 GMT

Rename Enterdate to something else so that it is no more part of synthetic key and then use that in your expression.

Re: Expression error

nareshthavidishetty — Mon, 21 Sep 2015 18:53:37 GMT

Hi sunny,

I have a column EnterDate in different tables,if i rename it as another name also synthetic keys will arise.

I want to use date field( EnterDate ) in expression.

Re: Expression error

sunny_talwar — Tue, 22 Sep 2015 03:19:55 GMT

I guess drop it from all other tables expect the one of the main table (may be your fact table)

Re: Expression error

nareshthavidishetty — Tue, 22 Sep 2015 06:49:24 GMT

Hi, its not working

if you need the script i will give it

Re: Expression error

sunny_talwar — Tue, 22 Sep 2015 06:53:27 GMT

Yes please share your script in a text file please

Re: Expression error

nareshthavidishetty — Wed, 23 Sep 2015 01:58:09 GMT

Please find the script and table structure and this Script is a two table script and above posted the the expression which i used on front end expression

AuditCards_Raw:

LOAD

'Customer Sat MTD Total(Inc3rdParty)' as dimsname,

'>= 90%' as 'Goals',

[ServiceOrderNo],

[Center],

DATE([EnterDate],'MM/DD/YYYY') AS [EnterDate],

[Satisfied] ,

[Efficiency],

[Attitude],

[Satisfied_Base],

[TechNo],

[TYPE] as TechTypeName

//[Tech_Home] as [ServiceCenter]

FROM

$(vQVDPath)\AuditCards_Servicecard_new.QVD (qvd) where match([Center],'801','805','816','806','807','810','824','832','850','879',808) and match ([TYPE],'SS','QQ','AA','ZZ','CC','GG');

Concatenate(AuditCards_Raw)

load * Inline [

dimsname,Goals, ServiceOrderNo, Center, EnterDate, Satisfied , Efficiency, Attitude, Satisfied_Base, TechNo, TechTypeName

'Customer Sat MTD Total(Inc3rdParty)','>= 70%',808]

left join(AuditCards_Raw)

//*********************ServiceCenter Names*****************************//

ServiceCenterNames:

LOAD * INLINE [

Center, CenterName

801, A

805, B

816, C

806, D

807, D

810, E

824, F

832, G

850, H

879, I

808,J

];

AuditCard:

LOAD *,

[EnterDate] &'_'& CenterName as %key

Resident AuditCards_Raw;

DROP Table AuditCards_Raw;

DROP Field EnterDate From AuditCard;

//EXIT Script;

//*****************************3RD Party *****************************//

ThirdPartyAuditCards:

LOAD

[ServiceOrderNo] as [ThirdPartySO#],

[Center] as [ThirdPartyCenter],

DATE( [EnterDate],'MM/DD/YYYY') AS [EnterDate],

[AuditCardTechNo] as [ThirdPartyAuditTechNo],

[Satisfied] as [ThirdPartySatisfied],

[Efficiency] as [ThirdPartyEfficiency],

[Attitude] as [ThirdPartyAttitude],

[Satisfied_Base] as [ThirdPartySatisfiedBase],

[TechNo] as [ThirdPartyTechNo],

[CREATE_DATE],

[CREATE_TIME]

//[Tech_Home] as [ServiceCenter]

FROM

$(vQVDPath)\AuditCards_ThirdParty_Servicecard_old.QVD (qvd) where match([Center],801,805,806,816,810,807,808,824,832,850,879,808);

left join(ThirdPartyAuditCards)

//*********************3rd Party ServiceCenter Names*****************************//

ThirdPartyServiceCenterNames:

LOAD * INLINE [

ThirdPartyCenter, ThirdCenterName

801, A

805, B

816, C

806, D

807, D

810, E

824, F

832, G

850, H

879, I

808,J

];

ThirdPartyAuditCards_Final:

Load *,

[EnterDate] & '-' & ThirdCenterName as %key

Resident ThirdPartyAuditCards;

DROP Table ThirdPartyAuditCards ;

DROP Field EnterDate From ThirdPartyAuditCards_Final;

//***********************************Excluding Third********************************************************//

AuditCardExcludingThird:

LOAD

'Customer Sat MTD Total(No3rdParty)' as Dimname,

'>= 90%' as Goal,

[ThirdPartySO#] as ExcludeThirdPartySO,

CenterName as ExcludeCenter,

pick(match(CenterName,1,5,6,10,24,32,50,79,96),'A','B','C','D','E','F','G','H','I') as ExcludeCenterName,

DATE([EnterDate],'MM/DD/YYYY') AS [EnterDate],

[ThirdPartySatisfied] as ExcludeThirdPartySat,

[ServiceOrderNo] as ExcludeSO,

[Satisfied] as ExcludeSAT

FROM

$(vQVDPath)\Servicecard_ExcludeThird.QVD (qvd) where match([CenterName],1,5,6,10,24,32,50,79,96);

Concatenate (AuditCardExcludingThird)

load * Inline [

Dimname,Goal, ExcludeThirdPartySO, ExcludeCenter, EnterDate,ExcludeThirdPartySat,ExcludeSO,ExcludeSAT

'Customer Sat MTD Total(No3rdParty)','>= 90%','ThirdParty',96]

;

AuditCardExcludingThird_Final:

Load *,

[EnterDate] & '-'& ExcludeCenterName as %key

Resident AuditCardExcludingThird;

DROP Table AuditCardExcludingThird;

DROP Field [EnterDate] from AuditCardExcludingThird_Final;

Reply • Like Show 0 Likes(0)