topic Re: Extracting data through Mid() and Sub-string() in QlikView

Extracting data through Mid() and Sub-string()

nareshthavidishetty — Wed, 23 Sep 2015 05:17:37 GMT

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.

Re: Extracting data through Mid() and Sub-string()

arulsettu — Wed, 23 Sep 2015 05:19:21 GMT

try this

=SubField('01/02/2015_City','_',1)

=mid(Left('01/02/2015_City',10),1)

Re: Extracting data through Mid() and Sub-string()

sunny_talwar — Wed, 23 Sep 2015 05:19:32 GMT

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

Re: Extracting data through Mid() and Sub-string()

qlikmsg4u — Wed, 23 Sep 2015 05:19:37 GMT

You can use

Subfield(Yourfield,'_',1)

Re: Extracting data through Mid() and Sub-string()

Digvijay_Singh — Wed, 23 Sep 2015 05:22:20 GMT

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.

Re: Extracting data through Mid() and Sub-string()

Anonymous — Wed, 23 Sep 2015 05:25:16 GMT

=SubField('01/02/2015_City','_')

Re: Extracting data through Mid() and Sub-string()

nareshthavidishetty — Wed, 23 Sep 2015 05:34:09 GMT

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;

Re: Extracting data through Mid() and Sub-string()

nareshthavidishetty — Wed, 23 Sep 2015 06:35:03 GMT

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;

Re: Extracting data through Mid() and Sub-string()

PrashantSangle — Wed, 23 Sep 2015 06:46:29 GMT

Hi,

you dont need use sub field or mid()

just use EnterDate field for Date.

Regards

Re: Extracting data through Mid() and Sub-string()

nareshthavidishetty — Wed, 23 Sep 2015 07:23:49 GMT

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.

Re: Extracting data through Mid() and Sub-string()

PrashantSangle — Wed, 23 Sep 2015 07:28:56 GMT

Hi,

You need to apply link table concept.

Regards