Expression error

[nareshthavidishetty]

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.

[nareshthavidishetty]

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

[nareshthavidishetty]

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

[nareshthavidishetty]

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

[sunny_talwar]

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

[nareshthavidishetty]

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.

[sunny_talwar]

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

[nareshthavidishetty]

Hi, its not working

if you need the script i will give it

[sunny_talwar]

Yes please share your script in a text file please

[nareshthavidishetty]

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)