Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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
Champion II
Champion II

So what output are you expecting?

Champion II
Champion II

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;

Specialist III
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]

;

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;

Partner
Partner

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%

Specialist III
Specialist III

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

Partner
Partner

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

Specialist III
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]

;

Champion II
Champion II

OK

Create a Straight table

Dimension

     Job Code

         Period

Expression

          max( TOTAL <[Job code]> Realisation)