Subquery to compare Data from same qvd

[#1]

Hi.

first of all thanks for the attention 🙂

I have this problem(i try ro semplify the question):

There is a one QVD with FIELDS

CLIENT, DEPARTMENT, FACILITY, YEAR(value can be only 2014 and 2013)

is necessary to create a Flag on CLIENT-DEPARTMENT Level that shoulbe be have this value:

-"New" if CLIENT in 2014 isn't present in 2013

-"Expanded" if CLIENT in 2014 is present in 2013 but with Different DEPARTMENT

-"Ext Fac" if CLIENT + DEPARTMENT in 2014 is present in 2013 but with a FACILITY that not present in 2014

-"Ent Fac" if CLIENT + DEPARTMENT in 2014 is present in 2013 but with a FACILITY that non PRESENT in 2013

-"Change Fact" if CLIENT + DEPARTMENT in 2014 is present in 2013 but with a different Facility of 2014

-"False" no condition above is satisfied

in SQL i can do more subquery but with LOAd i don't have any idea......

Many thanks

[#2]

QvdTable:

LOAD

Key,

f1,f2,f3

from

[qvd];

MyData:

left join (QvdTable)

Load

Key,

f1,f2,f3,

calculated expression...............

Resident QvdTable;

Use Resident of already loaded data to manipulate