Previous or other function?

[Anonymous]

  Hi all,

I have this:

CompanyID	JobDate	Norm
1234	13-2-2013	2
5678	5-1-2013	2
5678	1-6-2015	3

I want this:

CompanyID	JobDate	Norm
123	13-2-2013	2
123	1-1-2014	2
123	1-1-2015	2
5678	5-1-2013	2
5678	1-1-2014	2
5678	1-6-2015	3

In the first table table I have only record at Jobdate, bus for an intervalmatch() I need also records for all the other years till now.

Thanks!

Best regards,

Arjan

[maxgro · Accepted solution]

RESULT

SCRIPT

source:

LOAD Distinct

  CompanyID

FROM source.qvd (qvd);

tmp: load min(year(JobDate)) as MinJobYear From source.qvd (qvd);

let vMinJobYear = Peek('MinJobYear');

DROP Table tmp;

join (source)

load $(vMinJobYear) + rowno() -1 as JobYear

AutoGenerate (year(Today())-$(vMinJobYear)+1);

Left join (source)

LOAD

  CompanyID,

    JobDate,

  year(JobDate) as JobYear,

    Norm

From source.qvd (qvd);

final:

NoConcatenate load

  CompanyID,

    if(len(trim(JobDate))=0, date(MakeDate(JobYear)), JobDate) as JobDate,

  JobYear,

    if(len(trim(JobDate))=0, peek(Norm), Norm) as Norm

Resident source

order by CompanyID,   JobYear; 

DROP Table source;

[Anonymous]

Thanks Massimo Grossi!!