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
vinieme12
Champion III
Champion III

So what output are you expecting?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Do you only want to load data for latest date?

then try below

MaxDT:

LOAD Max(  Period) as MaxPeriod

FROM

[xxxxxx\Realizations by month.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD [Job code],

     Realisation,

     Period

FROM

[xxxxx\Realizations by month.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Exists(MaxPeriod,Period);

////////////////////////////////////////////////////////////

OR using KEEP keyword

/////////////////////////////////////////////////////////

MaxDT:

LOAD Max(  Period) as Period

FROM

(ooxml, embedded labels, table is Sheet1);

LEFT KEEP

LOAD [Job code],

     Realisation,

     Period

FROM

(ooxml, embedded labels, table is Sheet1);

Drop table MaxDT;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
marcus_malinow
Partner - Specialist III
Partner - Specialist III

How about this:

Realization:

LOAD [Job code],

     Realisation,

     Date(Period, 'MMM-YYYY') as Period

FROM

(ooxml, embedded labels, table is Sheet1);

INNER JOIN (Realization)

LOAD [Job code],

    Max(Period) as Period

RESIDENT Realization

WHERE Realisation > 0

GROUP BY [Job code]

;

its_anandrjs

You have to try this ways with your "JobCodes"

Source:

LOAD [Job code],

     Realisation,

     Date(Period) as Period

FROM

[Realizations by month.xlsx]

(ooxml, embedded labels, table is Sheet1);

MaxTable:

LOAD

[Job code],

Date(Max(Period)) as MaxDate

Resident Source

Group By [Job code]

Order By [Job code];

NoConcatenate

FinalTable:

LOAD

*

Resident Source Where Exists(MaxDate,Period);

DROP Tables Source,MaxTable;

rassanbekov
Partner - Contributor
Partner - Contributor
Author

I need the realization rate out of maximum date for each job code.

For example,

Code      Date                    Realization rate

1              1 January               20%

1              1 February             30%

Output shoul be like

Code      Date                    Rellization rate

1               1 February           30%

marcus_malinow
Partner - Specialist III
Partner - Specialist III

See my code below, it will load the latest data per [Job Code]

rassanbekov
Partner - Contributor
Partner - Contributor
Author

Marcus, next step, I need to map your script to Advisory table?

Could you please give some advice how to do it more efficiently? Now I have error.

Here below my script

Realization:

LOAD [Job code],

     Realisation,

     Period

FROM

[Data\Realizations by month.xlsx]

(ooxml, embedded labels, table is Sheet1);

INNER JOIN (Realization)

LOAD [Job code],

    Max(Period) as Period

RESIDENT Realization

WHERE Realisation > 0

GROUP BY [Job code]

Realizations:

LOAD 

Realisation,

[Job code]&'_'&Period as KEY

FROM [Data\Realizations by month.xlsx] (ooxml, embedded labels, table is Sheet1);

MapRealization:

Mapping

LOAD Distinct KEY, Realisation

Resident Realizations; 

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,

    [Job code]&'_'&Period as KEY,

    ApplyMap('MapRealization', [Job code]&'_'&Period,40) as AdjRealization

FROM [Data\July'17 ch hours details_kz _emc.xls] (biff, embedded labels, table is Advisory$);

marcus_malinow
Partner - Specialist III
Partner - Specialist III

You're missing a semi-colon after the INNER JOIN

INNER JOIN (Realization)

LOAD [Job code],

    Max(Period) as Period

RESIDENT Realization

WHERE Realisation > 0

GROUP BY [Job code]

;

vinieme12
Champion III
Champion III

OK

Create a Straight table

Dimension

     Job Code

         Period

Expression

          max( TOTAL <[Job code]> Realisation)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.