Scipt Issue

[Krish2459_58]

Hi,

We are using the Intermatch in the below script.

AFter Implementing the logic I'm getting wrong [Current monthly amount] in the below script.

Could you help me if the below script requires any modification.

LET _mindate = makedate(year(today()),5);
LET _maxdate = makedate(year(today())+5,5);

LOAD

yearname(Month,0,5) as Year,
Month;

Temp1:
LOAD
MonthName('$(_mindate)',iterno()-1) as Month

autogenerate
1
While MonthName('$(_mindate)',iterno()-1) < '$(_maxdate)';

Temp:
LOAD
%LeaseKey,
Period,
Currency,
Amount ,
[Start date],
[End Date]
FROM [$(varProjectDataDir)PTRAC_QLIK_ContractItemObject.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

ExpTemp:
Load
%LeaseKey,
Period,
Amount,
[Start date],
[End date],
Currency

Resident Temp;

drop table Temp;
Left join

IntervalMatch(Month)

LOAD

[Start date],
[End date]

Resident
ExpTemp;

Exp_Temp2:
load

%LeaseKey,
Period,
Amount,
Amount as expense_amount,
[Start date],
[End date],
Currency,
Month

Resident ExpTemp;
Drop Table ExpTemp;

Left Join
load

Month,
Year

Resident Temp1;
Drop Table Temp1;

ExpenseYearlyTemp:
Load
%LeaseKey,

Sum(Amount) as Amount,
[Start date],
[End date],
Period,
Currency,
Month,
Year,
if (Period = 'Month', Sum(Amount) /1,
if (Period = 'Year', Sum(Amount) /12,
if (Period = 'Quarter', Sum(Amount) /3,
if (Period = 'Once', Sum(Amount) /12,Sum(Amount))))) as [Current monthly amount],
Currency,
Month as Expense_Month,
Year as Expense_Year

Resident Exp_Temp2
Group by %LeaseKey, Period,Currency,[Start date],[End date],Month,Year;

Expense:
Load

%LeaseKey,
Description,
Period,
Amount,
[Start date],
[End date],
Expense_Month,
Expense_Year,

[Current monthly amount]*[TS Currency Rate] as [Current monthly amount],

[TS Currency Rate],
Currency

Resident ExpenseYearlyTemp;
Drop Table Exp_Temp2,ExpenseYearlyTemp,CurrencyRates ;

[Oleg_Troyansky]

Hi Krish,

If I'm not mistaken, the problem might be happening here:

Temp:
LOAD
%LeaseKey,
Period,
Currency,
Amount ,
[Start date],
[End Date]
FROM [$(varProjectDataDir)PTRAC_QLIK_ContractItemObject.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

ExpTemp:
Load
%LeaseKey,
Period,
Amount,
[Start date],
[End date],
Currency

Resident Temp;

drop table Temp;

The second load (ExpTemp) has the same set of fields as the first load (Temp), therefore the data will get automatically concatenated to the first table Temp, and then this table is being dropped. It's a very common scripting "catch 22". I'm not sure that's the purpose of reloading data from Temp into ExpTemp, but if it's needed for any purpose, add the NOCONCATENATE keyword to prevent automatic concatenation.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Cheers,

[Krish2459_58]

Hi @Oleg_Troyansky ,

I think thats not a problem in the actual scipt.

Sharing the complete script.Please have a look.

LET _mindate = makedate(year(today()),5);

LET _maxdate = makedate(year(today())+5,5);

LOAD

yearname(Month,0,5) as Year,

Month;

Temp1:

LOAD 

MonthName('$(_mindate)',iterno()-1) as Month

autogenerate 

1

While MonthName('$(_mindate)',iterno()-1)  < '$(_maxdate)';

Temp:

LOAD

    // ContractItem_Reference,

    Contract_Reference as  %LeaseKey,

    ContractPeriod_Reference,

    ContractItem_ClientName,

    ContractItem_Region,

    ContractItem_SubRegion,

    ContractItem_SubSubRegion,

    ContractItem_AccountCode,

    ContractItem_ActualEndDate,

    ContractItem_ActualStartDate,

    ContractItem_Area,

    ContractItem_BankAccount,

    ContractItem_BudgetCode,

    ContractItem_Budget,

    ContractItem_BudgetItem,

    ContractItem_CodeType,

    ContractItem_Comments,

    ContractItem_Type,

    ContractItem_CostCenter,

    ContractItem_CostType,

    ContractItem_Currency as Currency ,

    ContractItem_Description,

//    if(Len(Trim(ContractItem_EndDate)) = 0, date(AddYears(Today(),+1)), date(ContractItem_EndDate)) as [End date],

        if(len(trim(ContractItem_EndDate)) = 0, date(AddYears(today(),1)), date(ContractItem_EndDate)) as [End date],

    ContractItem_ExternalReference,

    ContractItem_ItemReference,

    ContractItem_ItemName,

    ContractItem_Name as Description,

    ContractItem_OnHold,

    ContractItem_Price as Amount ,

    ContractItem_PriceIndex,

    ContractItem_PricePeriod,

    ContractItem_PricePeriodCode as Period,

    ContractItem_Quantity,

    date(ContractItem_StartDate) as [Start date],

    ContractItem_VATRate,

    makedate(year(today()),5) as ST_Date,

    makedate(year(today())+5,5) as ED_Date,

    today() as D1

FROM [$(varProjectDataDir)PTRAC_QLIK_ContractItemObject.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq)

Where ContractItem_ClientName='Doma' and 

Date(Today())>=Date([ContractItem_StartDate]) and 

  Date(Today())<=if(len(trim(ContractItem_EndDate)) = 0, date(AddYears(today(),1)), date(ContractItem_EndDate));

//   (Date(Today())<=Date([ContractItem_EndDate]) or Len(Trim(ContractItem_EndDate)) = 0);

NoConcatenate

ExpTemp:

Load

%LeaseKey,

 Description, 

 Period,

 Amount,

 [Start date],

 [End date],

 Currency

 Resident Temp;

 // where date([Start date] ,'MM/DD/YYYY') >='$(_mindate)' and date([Start date] ,'MM/DD/YYYY') <='$(_maxdate)';

drop table Temp;

Left join 

IntervalMatch(Month)

LOAD 

  [Start date],

  [End date]

Resident

ExpTemp;

Exp_Temp2:

load

%LeaseKey,

 Description, 

 Period,

 Amount,

 Amount as expense_amount,

 [Start date],

 [End date],

 Currency,

Month

Resident ExpTemp;

Drop Table ExpTemp;

Left Join

load

Month,

Year

Resident Temp1;

Drop Table Temp1;

ExpenseYearlyTemp:

Load

 1 AS #ExpenseYearlyCounter,

 %LeaseKey, 

 Description, 

 IF(WildMatch(Description,'*RENT*'),'Rent','OPEX') AS [Rent - OPEX],

 Period,

 Sum(Amount) as Amount,

 [Start date],

 [End date],

 if (Period = 'Month', Sum(Amount) *12,

  if (Period = 'Year', Sum(Amount) *1,

   if (Period = 'Quarter', Sum(Amount) *4,

    if (Period = 'Once', Sum(Amount) *1,Sum(Amount))))) as [Amount of Expense (annual)],

 if (Period = 'Month', Sum(Amount) /1,

  if (Period = 'Year', Sum(Amount) /12,

   if (Period = 'Quarter', Sum(Amount) /3,

    if (Period = 'Once', Sum(Amount) /12,Sum(Amount))))) as [Current monthly amount],

 Currency,

  Month as Expense_Month,

  Year as Expense_Year

Resident Exp_Temp2

Group by %LeaseKey, Period,Description,Currency,[Start date],[End date],Month,Year;

Left Join(ExpenseYearlyTemp)

Load

Currency,

[Currency Rate] as [TS Currency Rate]

Resident CurrencyRates; 

Left Join(ExpenseYearlyTemp)

Load

%LeaseKey,

Contract_OwnershipType as [Expense Lease Type]

Resident Lease;

Expense:

Load

  #ExpenseYearlyCounter,

  %LeaseKey,

  [Expense Lease Type],

  Description,

  [Rent - OPEX],

  Period, 

  Amount,

  [Start date],

  [End date],

  Expense_Month,

  Expense_Year,

  [Amount of Expense (annual)]*[TS Currency Rate] as [Amount of Expense (annual)],

  IF([Rent - OPEX] = 'Rent',[Amount of Expense (annual)]*[TS Currency Rate]) as [Rent Expense Amount],

  IF([Rent - OPEX] = 'OPEX',[Amount of Expense (annual)]*[TS Currency Rate]) as [OPEX Expense Amount],

  [Amount of Expense (annual)] as [Amount of Expense (annual - Local)],

  [Current monthly amount]*[TS Currency Rate] as [Current monthly amount],

  [TS Currency Rate],

  Currency

Resident ExpenseYearlyTemp; 

Drop Table Exp_Temp2,ExpenseYearlyTemp,CurrencyRates ;

[Krish2459_58]

Any help here.

Thanks...

[Oleg_Troyansky]

My friend, this is a bit too much to review and analyze and find "what might be wrong here". You'll have to put a bit more effort into it and come back with more specific pointed questions.

- Run your script through the Debugger and verify that the everything is populated the way you expected.

- Add variables with the row counters before and after every load, to determine whether you lose data or get any duplicated values

- Try moving the IntervalMatch statement to appear after several JOIN loads that involve Start Date and End Date - it's possible that you are breaking the intervalmatch logic with additional transformations after it.

You have to narrow the problem down to something more specific. Nobody can guess what might go wrong in such a long script.

Cheers,