topic Re: Load Calc field based on multiple criteria in QlikView

Load Calc field based on multiple criteria

Mon, 31 Mar 2014 11:53:46 GMT

Hi All

I have been kicking this question around for a while and I just cant seem to get it right - I hope someone can help.

I have multiple data files that is a log of applications - as part of the load I need to identify if an application is open and if so what Team it is with.

I need to create a calculated field during load based on this criteria :

For each application = the latest date where it is Status = RDY AND there is no CMP for a later date in Team Closed

Any advise relly appreciated

Thanks

Application	Team	Date	Status	Calculated field
1	Input	01/01/2014 09:00	CMP
2	Input	01/01/2014 10:00	RDY
1	Verify	01/01/2014 11:00	RDY
1	Doc	02/01/2014 10:00	RDY
1	Print	02/01/2014 12:00	CMP
2	Verify	02/01/2014 13:00	CMP
2	Doc	01/01/2014 11:30	RDY	OPEN
3	Verify	01/01/2014 12:30	RDY	OPEN
3	Print	02/01/2014 12:00	CMP
1	Closed	02/01/2014 12:00	CMP

Re: Load Calc field based on multiple criteria

marcus_sommer — Mon, 31 Mar 2014 14:23:18 GMT

I think this could be solved with inter-record-functions in (nested) if-loops in a sorted load. The condition isn't really clear to me, therefore as abstract example:

sourcedata:

Load * From source;

sourcedataSort:

Load

if(rowno() = 1, Status, if(Application = peek('Application', -1) and Status = 'XYZ', 'abc',

if(......................))) as [Calculated field]

Resident sourcedata Order by Application, Date;

- Marcus

Re: Load Calc field based on multiple criteria

Mon, 31 Mar 2014 14:36:01 GMT

Hi Marcus

Thanks for your response - can you please explain how the calculate Field is working - my understanding is the following :

If its the first row then take the status ( this will give a result either RDY or CMP )

or if the app number is not the same as the app number previous and the status us XYZ - 'ABC' .

I am wondering which is the part with the latest date ?

Can you help me apply the logic ?

Thanks

Re: Load Calc field based on multiple criteria

marcus_sommer — Mon, 31 Mar 2014 15:32:22 GMT

If(Application <> peek('Application', -1) then there is a new application with its startdate. Perhaps there is a better/easier way to check the latest date - per mapping and applymap:

MapLatestDate:

Mapping Load Application, max(Date) as MaxDate From Source Group By Application;

sourcedataSort:

Load

if(Date = applymap('MapLatestDate', Date) and Status = 'XYZ' and peek('Status', -1) = 'ABC', 'abc',

if(......................))) as [Calculated field]

Resident sourcedata Order by Application, Date;

- Marcus

Re: Load Calc field based on multiple criteria

Gysbert_Wassenaar — Mon, 31 Mar 2014 16:30:25 GMT

See attached qvw.

Re: Load Calc field based on multiple criteria

maxgro — Mon, 31 Mar 2014 21:02:59 GMT

see attachment

Re: Load Calc field based on multiple criteria

Tue, 01 Apr 2014 09:02:47 GMT

Hi Massimo and Gysbert,

Both look great I will have a look this morning and see if I can apply to my data - thanks so much for taking the time to draft samples - I will respond with my findings ( questions!)

Thanks again

Re: Load Calc field based on multiple criteria

Tue, 01 Apr 2014 10:54:44 GMT

Hi Massimo

Can you explain the alt part of the following ?

Thanks

and

MaxDateRDY

>=alt

(MaxDateCMP

)