Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rassanbekov
Partner - Contributor
Partner - Contributor

Loading latest date

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.

13 Replies
rassanbekov
Partner - Contributor
Partner - Contributor
Author

See, but Qlik says that I have a loop... Maybe excessive script has been written?

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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;

vinieme12
Champion III
Champion III

Check attached sample

Actual Data

   

Job code Realisation Period
AS005100180Dec-2016
AS005100140

Nov-2016

Expression

MAX(AGGR(max( TOTAL <[Job code]> Realisation) ,[Job code]))

maxdata.JPG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
rassanbekov
Partner - Contributor
Partner - Contributor
Author

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);