topic Re: Product not exists - add manual values zero in QlikView

Product not exists - add manual values zero

Thu, 21 May 2015 20:28:26 GMT

I have table

table1:

LOAD * INLINE [

month,rep,sales,product

Jan ,101,3000,p1

Jan,102,2340,p1

Jan,103,2450,p2

Jan,103,1200,p1

Jan,101,1211,p2

Jan,103,1111,p2

feb,102,5000,p1

feb,103,4000,p2

feb,102,2000,p3

feb,103,6543,p1

March,101,2310,p1

March,103,1200,p2

];

Rep 101 does not sell anything on month of Feb.

I have a list of products

p1,p2,p3,p4,p5

I want to add the product p1,p2,p3,p4,p5 value 0 for feb for Rep 101 and

March rep 101 already has sold product p1 -but I want to add p2,p3,p4,p5 for sales 0 for March.

I have millions of records. Looping will kill my performance for comparing the products.

Please advice is there any better way to handle this situations.

Re: Product not exists - add manual values zero

Clever_Anjos — Thu, 21 May 2015 20:44:34 GMT

We should verify the performance:

tab:

LOAD * INLINE [

month,rep,sales,product

Jan ,101,3000,p1

Jan,102,2340,p1

Jan,103,2450,p2

Jan,103,1200,p1

Jan,101,1211,p2

Jan,103,1111,p2

feb,102,5000,p1

feb,103,4000,p2

feb,102,2000,p3

feb,103,6543,p1

March,101,2310,p1

March,103,1200,p2

];

// a table with all months, all products, all reps

load Distinct month Resident tab;

join load Distinct rep Resident tab;

join load Distinct product Resident tab;

Concatenate(tab)

load *, 0 as sales Resident t;

drop Table t;

Re: Product not exists - add manual values zero

Anonymous — Thu, 21 May 2015 21:29:50 GMT

Are you sure you need all these 0's? See how a pivot table may look like just with your sample data, zeroes are there:

Re: Product not exists - add manual values zero

petter — Thu, 21 May 2015 21:32:51 GMT

This should work in a load script:

table1:

LOAD

AutoNumber( month & '|' & product & '|' & rep ) AS %Key,

INLINE [

month,rep,sales,product

Jan ,101,3000,p1

Jan,102,2340,p1

Jan,103,2450,p2

Jan,103,1200,p1

Jan,101,1211,p2

Jan,103,1111,p2

feb,102,5000,p1

feb,103,4000,p2

feb,102,2000,p3

feb,103,6543,p1

March,101,2310,p1

March,103,1200,p2

];

Products:

LOAD DISTINCT

product

RESIDENT

table1;

Months:

LOAD DISTINCT

month

RESIDENT

table1;

Reps:

LOAD DISTINCT

rep

RESIDENT

table1;

nProds = NoOfRows('Products');

nMonths = NoOfRows('Months');

nReps = NoOfRows('Reps');

LOAD

WHERE Not( Exists( %Key ));

LOAD

AutoNumber(month & '|' & product & '|' & Peek(rep,IterNo()-1,'Reps')) AS %Key,

month,

product,

Peek(rep,IterNo()-1,'Reps') AS rep,

0 AS sales

WHILE IterNo()<=$(nReps);

LOAD

month,

Peek(product,IterNo()-1,'Products') AS product

WHILE IterNo()<=$(nProds);

LOAD

Peek(month,RecNo()-1,'Months') AS month

AUTOGENERATE

$(nMonths);

// IF NOT NEEDED DROP THE HELPER TABLES...

DROP TABLES Products, Months, Reps;

Re: Product not exists - add manual values zero

Anonymous — Thu, 21 May 2015 21:43:38 GMT

One more version, rather simple script additions, no loops, to make dimension selections mutually independent, so you can see more 0's

Re: Product not exists - add manual values zero

petter — Thu, 21 May 2015 21:47:05 GMT

that's how it should be done

Re: Product not exists - add manual values zero

Anonymous — Thu, 21 May 2015 21:51:21 GMT