Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ;
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,
Hi @Oleg_Troyansky ,
I think thats not a problem in the actual scipt.
Sharing the complete script.Please have a look.
Any help here.
Thanks...
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,