Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
please help to load the script from file:
1. I need to load latest available date "realization" rate from input data in terms of "job codes"
Please see below input file from which we need select out the maximum available date.
Hope on your assistance.
See, but Qlik says that I have a loop... Maybe excessive script has been written?
Excessive script? Not sure what you mean, but I can see where your loop has occurred.
Add this to the end of your script:
DROP TABLE Realizations;
Check attached sample
Actual Data
Job code | Realisation | Period |
AS0051001 | 80 | Dec-2016 |
AS0051001 | 40 | Nov-2016 |
Expression
MAX(AGGR(max( TOTAL <[Job code]> Realisation) ,[Job code]))
I included the statement and script worked. Now I need to use applymap for calclation: Hrs x [Rate (KZT)] x Realisation.
But if the job codes does not match, then the realisation should equal to '40'.
Could you please how can I fix the script?
Advisory:
LOAD
BU,
[Client name],
[Job code],
[Eng. name],
[Work dt],
Period,
Month(Period) as Date,
QuarterStart(Period) as Quarter,
Hrs,
Tr.Reference,
Tr.Type,
Descript,
Ref.,
Entity,
[Tr type],
[Journ Type],
[Empl code],
[Empl name],
[Empl office],
[Empl dep],
[Eng CC],
EngMan
FROM [Data\July'17 ch hours details_kz _emc.xls] (biff, embedded labels, table is Advisory$);
Inner Join (Advisory)
MC_Employees:
LOAD [Empl name],
Grade,
City,
Group,
Month(Period) as Date
FROM [Data\MC employee list.xlsx] (ooxml, embedded labels, table is Sheet1);
MC_Rates:
LOAD
Position as Grade,
[Rate (KZT)]
FROM [Data\MC rates in KZT.xlsx] (ooxml, embedded labels, table is Sheet1);
Realization:
LOAD
[Job code],
Realisation,
Period
FROM
[Data\Realizations by month.xlsx]
(ooxml, embedded labels, table is Sheet1);
INNER JOIN (Realization)
Realizations:
LOAD [Job code],
Max(Period) as Period
RESIDENT Realization
WHERE Realisation > 0
GROUP BY [Job code];
Inner join (Advisory)
NewRealization:
Load
[Job code],
Realisation,
Period
Resident Realization;
DROP Table Realization;
Budget:
LOAD Month(Date) as Date, Group, Amount as BudgetAmount
FROM Data\Budget.xlsx (ooxml, embedded labels, table is Sheet1);