topic Re: Loading latest date in QlikView

Loading latest date

rassanbekov — Tue, 29 Aug 2017 14:34:38 GMT

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.

Re: Loading latest date

vinieme12 — Tue, 29 Aug 2017 14:41:10 GMT

So what output are you expecting?

Re: Loading latest date

vinieme12 — Tue, 29 Aug 2017 14:44:37 GMT

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;

Re: Loading latest date

marcus_malinow — Tue, 29 Aug 2017 14:48:47 GMT

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]

;

Re: Loading latest date

its_anandrjs — Tue, 29 Aug 2017 15:03:21 GMT

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;

Re: Loading latest date

rassanbekov — Tue, 29 Aug 2017 15:03:33 GMT

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%

Re: Loading latest date

marcus_malinow — Tue, 29 Aug 2017 15:12:08 GMT

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

Re: Loading latest date

rassanbekov — Tue, 29 Aug 2017 15:19:51 GMT

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

Re: Loading latest date

marcus_malinow — Tue, 29 Aug 2017 15:23:30 GMT

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]

;

Re: Loading latest date

vinieme12 — Tue, 29 Aug 2017 15:24:52 GMT

Create a Straight table

Dimension

Job Code

Period

Expression

max( TOTAL <[Job code]> Realisation)

Re: Loading latest date

rassanbekov — Tue, 29 Aug 2017 15:25:05 GMT

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

Re: Loading latest date

marcus_malinow — Tue, 29 Aug 2017 15:40:42 GMT

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;

Re: Loading latest date

vinieme12 — Tue, 29 Aug 2017 15:46:17 GMT

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

Re: Loading latest date

rassanbekov — Tue, 29 Aug 2017 16:08:24 GMT

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