Logic at script level

[nareshthavidishetty]

Hi,

We were having an requirement.

We hvave two fileds Distict and Location here Location YUMA from district Arizona 2 wants to shift to district Arizona 1 means YUMA should come under Arizona 1.

We need to done this at QV application level.

Thanks...

[sunny_talwar]

May be like this:

LOAD Location,

          If(WildMatch(Location, '*135*'), 'Arizona 1', District) as District

FROM Source;

Update made after seeing Max's response

[sunny_talwar]

or this

If(Match(Location, '135 YUMA'), 'Arizona 1', District) as District

Update made after seeing Max's response

[PrashantSangle]

Hi,

not sure , but try like

if(location='135 YUMA','Arizona 1',District) as newDistrict

Regards

[nareshthavidishetty]

Hi,

I have tried the below logic but it showing no results..

If(Match(LocationNbr & ' ' & LocationName3, '135 YUMA'), 'Arizona 1', District_QuotaTracker) as District

Thanks..

[sunny_talwar]

Can you share a sample where is isn't working

[nareshthavidishetty]

Below is the script..

Tmp1:

LOAD [Reported Date],

  Date#(Num(Month([Reported Date]))&'/01/'&(Year([Reported Date])-1), 'MM/DD/YYYY') as LastYearReportingDate,

     LocationNbr,

     Quota,

     [Posted Volume],

      if (LocationNbr ='016', ' ', SameStoreSalesTY) as SameStoreSalesTY,

     [Should Be],

     [Days Ahead/Behind],

     [Last Year Total],

   if (LocationNbr ='016', ' ',[Last Year MTD]) as [Last Year MTD],

     [% of LY Sales],

     [On Track to Post],

     DaysInMonth,

     DailyIndex,

     ExtendedAmt,

     TotalBookedAmt

FROM

[..\..\QVD\Retail Sales\AS400_Quota_Tracker.QVD] (qvd)

Where [Reported Date]= Today()-1;

inner join (Tmp1)

LOAD LocationNbr,

     LocationName1,

     LocationName2,

    // LocationName3,

    if(LocationName3='SA W COMMERCE','410 & MARBACH',LocationName3) as LocationName3,

     State,

     RetailSF,

     TotalSF,

     Retail_Range,

     Age,

     Age_Range,

     Remodeled,

     Status,

     [Store Open],

     Date#(Num(Month(AddMonths([Store Open],1)))&'/01/'&Year(AddMonths([Store Open],1)),'MM/DD/YYYY') As Store_FirstFullMonthOpen,    

     [Store Close],

     District_AS400,

     District_RetailScorecardReporting,

     District_RetailScorecardDMEmpNbr,

     District_GeneralReporting,

     District_RetailScorecard,

     District_Advertising,

     District_QuotaTracker,

    // LocationNbr & ' ' & LocationName3,

//If(Match(LocationNbr & ' ' & LocationName3, '135 YUMA'), 'Arizona 1', District_QuotaTracker) as District,

if(LocationName3='YUMA','Arizona 1',District_QuotaTracker) as newDistrict,

     Primary_Warehouse,

     Primary_Service,

     Classification,

     [Address Line 1],

     [Address Line 2],

     City,

     State1,

     Zip,

     County,

     Latitude,

     Longitude

FROM

[..\..\QVD\Locations\Spreadsheet_Locations.QVD]

(qvd);

Quota_Tracker:

LOAD *,

  If(LastYearReportingDate >= Store_FirstFullMonthOpen, 'Y','N') As [Same Store Sales Calc]

Resident Tmp1;

drop table Tmp1;

Thanks..

[nareshthavidishetty]

Hi,

When use the logic with below script(Simple extract) its working..

LOAD LocationNbr,

     if(LocationName3='SA W COMMERCE','410 & MARBACH',LocationName3) as LocationName3,    

     If(Match(LocationName3, 'YUMA'), 'Arizona 1', District_QuotaTracker) as District ,     

     District_QuotaTracker,

     Longitude

FROM

[..\..\QVD\Locations\Spreadsheet_Locations.QVD]

(qvd);

But when i use the below Script its not working..

Tmp1:

LOAD [Reported Date],

  Date#(Num(Month([Reported Date]))&'/01/'&(Year([Reported Date])-1), 'MM/DD/YYYY') as LastYearReportingDate,

     LocationNbr,

       if (LocationNbr ='016', ' ',[Last Year MTD]) as [Last Year MTD],

       TotalBookedAmt

FROM

[..\..\QVD\Retail Sales\AS400_Quota_Tracker.QVD] (qvd)

Where [Reported Date]= Today()-1;

inner join (Tmp1)

LOAD LocationNbr,

     if(LocationName3='SA W COMMERCE','410 & MARBACH',LocationName3) as LocationName3,    

     If(Match(LocationName3, 'YUMA'), 'Arizona 1', District_QuotaTracker) as District ,     

     District_QuotaTracker,

     Longitude

FROM

[..\..\QVD\Locations\Spreadsheet_Locations.QVD]

(qvd);

quota_Tracker:

LOAD *,

  If(LastYearReportingDate >= Store_FirstFullMonthOpen, 'Y','N') As [Same Store Sales Calc]

Resident Tmp1;

drop table Tmp1;

Thanks..

[sunny_talwar]

Could it because of the inner join that the whole row is getting removed? Can you try just the Join

[#10]

Hi,

if(location='135 YUMA','Arizona 1',District) as Districtnew

Regards,

Snehal Nabar