Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load Subset of data by One field Values

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
*/


4 Replies
MVP
MVP

Re: Load Subset of data by One field Values

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;

Not applicable

Re: Load Subset of data by One field Values

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

qlikpahadi07
Valued Contributor

Re: Load Subset of data by One field Values

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 :**************************************************

DivisionCountryPeriodSales
LAMexico201007100
LAMexico201008400
LAMexico201009900
LAMexico2010101000
Not applicable

Re: Load Subset of data by One field Values

Just use

Where Not Exists (Period);

Community Browser