Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I created a composite key with Date and City and it look like 01/02/2015_City.
Now i want to fetch the only date field only by using Mid() and subfield() functions.
try this
=SubField('01/02/2015_City','_',1)
or
=mid(Left('01/02/2015_City',10),1)
For date you can try this:
Date#(SubField('01/02/2015_City', '_', 1), 'MM/DD/YYYY') assuming your date is in MM/DD/YYYY format. If it is DD/MM/YYYY, please change accordingly.
For doing it in the script, try this:
Date#(SubField(FieldName, '_', 1), 'MM/DD/YYYY') as DateField
You can use
Subfield(Yourfield,'_',1)
I think you don't need it as Date should be available directly. When we create composite key we can keep individual fields in one of the table.
=SubField('01/02/2015_City','_')
Below is the script where can i use this in the script
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],'OTA','OTE','OTL','INA','INE','INL');
Concatenate(AuditCards_Raw)
load * Inline [
dimsname,Goals, ServiceOrderNo, Center, EnterDate, Satisfied , Efficiency, Attitude, Satisfied_Base, TechNo, TechTypeName
'Customer Sat MTD Total(Inc3rdParty)','>= 90%',808]
;
left join(AuditCards_Raw)
//*********************ServiceCenter Names*****************************//
ServiceCenterNames:
LOAD * INLINE [
Center, CenterName
801, Beaumont
805, Houston
816, Houston
806, San Antonio
807, San Antonio
810, Dallas
824, El Paso
832, Phoenix
850, Charlotte
879, McAllen
808,ThirdParty
];
AuditCard:
LOAD *,
[EnterDate] &'_'& CenterName as %key
Resident AuditCards_Raw;
DROP Table AuditCards_Raw;
DROP Field EnterDate From AuditCard;
Where can place the expression in the script Date#(SubField(%key, '_', 1), 'MM/DD/YYYY') as DateField,below is the script
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
FROM
$(vQVDPath)\AuditCards_Servicecard_new.QVD (qvd) where match([Center],'801','805','816','806','807','810','824','832','850','879',808) and match ([TYPE],'OTA','OTE','OTL','INA','INE','INL');
Concatenate(AuditCards_Raw)
load * Inline [
dimsname,Goals, ServiceOrderNo, Center, EnterDate, Satisfied , Efficiency, Attitude, Satisfied_Base, TechNo, TechTypeName
'Customer Sat MTD Total(Inc3rdParty)','>= 90%',808]
;
left join(AuditCards_Raw)
//*********************ServiceCenter Names*****************************//
ServiceCenterNames:
LOAD * INLINE [
Center, CenterName
801, A
805, B
806, C
816, D
810, E
807, F
824, G
832, H
850, I
879, J
808, K
];
AuditCard:
LOAD *,
[EnterDate] &'_'& CenterName as %key
Resident AuditCards_Raw;
DROP Table AuditCards_Raw;
DROP Field EnterDate From AuditCard;
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
806, C
816, D
810, E
807, F
824, G
832, H
850, I
879, J
808, K
];
ThirdPartyAuditCards_Final:
Load *,
[EnterDate] & '-' & ThirdCenterName as %key
Resident ThirdPartyAuditCards;
DROP Table ThirdPartyAuditCards ;
DROP Field EnterDate From ThirdPartyAuditCards_Final;
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;
Hi,
you dont need use sub field or mid()
just use EnterDate field for Date.
Regards
I drop the field EnterDate why means every table i have EnterDate and most of the tables CenterName so because of that i made a composite key on top of EnterDate And CenterName and i name it as %key and i done the same logic in every table which resolve my synthetic key problem and i joined all the tables with %key.
Below is the table structure.