topic Re: Logic at script level in QlikView

Logic at script level

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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...

Re: Logic at script level

sunny_talwar — Thu, 12 May 2016 09:22:19 GMT

May be like this:

LOAD Location,

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

FROM Source;

Update made after seeing Max's response

Re: Logic at script level

sunny_talwar — Thu, 12 May 2016 09:23:01 GMT

or this

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

Update made after seeing Max's response

Re: Logic at script level

PrashantSangle — Thu, 12 May 2016 09:24:07 GMT

Hi,

not sure , but try like

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

Regards

Re: Logic at script level

nareshthavidishetty — Thu, 12 May 2016 09:43:25 GMT

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..

Re: Logic at script level

sunny_talwar — Thu, 12 May 2016 09:44:37 GMT

Can you share a sample where is isn't working

Re: Logic at script level

nareshthavidishetty — Thu, 12 May 2016 09:45:31 GMT

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..

Re: Logic at script level

nareshthavidishetty — Thu, 12 May 2016 10:17:44 GMT

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..

Re: Logic at script level

sunny_talwar — Thu, 12 May 2016 10:27:18 GMT

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

Re: Logic at script level

Thu, 12 May 2016 10:55:04 GMT

Hi,

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

Regards,

Snehal Nabar

Re: Logic at script level

nareshthavidishetty — Thu, 12 May 2016 13:01:14 GMT

Hi,

By keeping only join also it give same result.

Thanks..