topic Re: Load Subset of data by One field Values in QlikView

Load Subset of data by One field Values

Sat, 21 Sep 2013 02:34:10 GMT

LOAD * INLINE [
   Division, Country, Period, Sales
      LA,       Mexico, 201009, 900
      LA,       Mexico, 201010, 1000];

Concatenate
Old_Data:
LOAD Division,
     Country,
     Period,
    // Period as Alias,
     Sales
FROM
Current_Data.qvd
(qvd)
Where not Exists (Alias,Period);
/*
Current_Data.qvd contains data below but i only want subset of data . only those rows where Period value does not exist in above New_Data
So i would not want row with period 201009 becasue it exist in above table.. Please advise
Division Country Period Sales
LA Mexico 201007 100
LA Mexico 201008 400
LA Mexico 201009 800
*/

Re: Load Subset of data by One field Values

swuehl — Sat, 21 Sep 2013 12:20:58 GMT

Not sure if I completely understood your request, you want to check, if the combination of all fields (except Sales values), can be found in the inline table?

Then try creating an appropriate key:

NEW:

LOAD *, AutoNumberHash128(Division,Country,Period) as Key INLINE [

Division, Country, Period, Sales

LA, Mexico, 201009, 900

LA, Mexico, 201010, 1000

];

Concatenate

Old_Data:

LOAD Division,

Country,

Period,

Sales

INLINE [

Division, Country, Period, Sales

LA, Mexico, 201007, 100

LA, Mexico, 201008, 400

LA, Mexico, 201009, 800

]

Where not Exists (Key,AutonumberHash128(Division,Country,Period));

drop field Key;

Re: Load Subset of data by One field Values

Sun, 22 Sep 2013 02:25:35 GMT

Sorry I commented Alias field.. but inshort i want to load only those rows which are not there in first table by Period.

Re: Load Subset of data by One field Values

qlikpahadi07 — Sun, 22 Sep 2013 11:23:35 GMT

I hope I understood you requirement else you attach a Output

find this :

Historic_Data:

LOAD * INLINE [

Division, Country, Period, Sales

LA, Mexico, 201009, 900

LA, Mexico, 201010, 1000];

New_Data:

LOAD * INLINE [

Division, Country, Period, Sales

LA, Mexico, 201007, 100

LA, Mexico, 201008, 400

LA, Mexico, 201009, 800

] Where not Exists (Period,Period);

your output will be :**************************************************

Division	Country	Period	Sales
LA	Mexico	201007	100
LA	Mexico	201008	400
LA	Mexico	201009	900
LA	Mexico	201010	1000

Re: Load Subset of data by One field Values

Sun, 22 Sep 2013 19:27:25 GMT

Just use

Where Not Exists (Period);