topic Re: Previous or other function? in QlikView

Previous or other function?

Anonymous — Tue, 02 Jun 2015 10:10:09 GMT

Hi all,

I have this:

I want this:

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 — Tue, 02 Jun 2015 10:31:40 GMT

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 — Tue, 02 Jun 2015 10:53:48 GMT

Thanks Massimo Grossi!!