topic Re: Need Help in Scripting in QlikView

Need Help in Scripting

Anonymous — Tue, 06 Sep 2016 09:53:34 GMT

Hi Expert

I have product list with 63 items (fixed). and customer list is 150 (fixed)

and i have trans table as below.Each customer would not get transaction for all the product items (that means 63) each month. In trans table i have to add the remaining(missing) product items for each customer though transaction made or not.

if i make my master table [150 customer * 63 product items] and then right join with trans table i would achieve the result. But it is increasing my master rows. There is any way only with product table i can reaming items in trans table for each customer

Please help me how can we achieve it script

TransTable

Month	Product	Customer	Amt
April	P001	C001	5
April	P002	C001	1
April	P003	C001	1
April	P004	C001	1
April	P001	C002	1
April	P002	C002	1
April	P004	C002	1

Regards

Ramu

Re: Need Help in Scripting

tresB — Tue, 06 Sep 2016 10:04:17 GMT

You can probably use left join with master table instead.

Re: Need Help in Scripting

Anil_Babu_Samineni — Tue, 06 Sep 2016 10:21:17 GMT

You can try with left join

Main:

Load * from source;

Left join(main)

Load *, fields from master calendar ;

Re: Need Help in Scripting

effinty2112 — Tue, 06 Sep 2016 10:38:02 GMT

Hi Ramu,

What about something like:

Temp:

Load

Product,

Customer,

0 as Zero,

'April' as Month

Resident Master;

Left Join(Temp)

Load * Resident TransTable;

TransTable1:

Load

Month,

Product,

Customer,

RangeSum(Amt, Zero) as Amt

Resident Temp;

Good luck

Andrew

Re: Need Help in Scripting

maxgro — Tue, 06 Sep 2016 11:38:29 GMT

test data (products, customers)

Products:

// P001 to P006

load 'P00' & rowno() as Product AutoGenerate 6;

Customers:

// C001 to C005

load 'C00' & rowno() as Customer AutoGenerate 5;

// your data, I added one row, may

TransTable:

load * inline [

Month, Product, Customer, Amt

April, P001, C001, 5

April, P002, C001, 1

April, P003, C001, 1

April, P004, C001, 1

April, P001, C002, 1

April, P002, C002, 1

April, P004, C002, 1

May, P004, C002, 1

];

// all months x products x customers

Final:

LOAD Distinct Month Resident TransTable;

join (Final) LOAD Product Resident Products;

join (Final) LOAD Customer Resident Customers;

// add trans table amount

left Join (Final) load * Resident TransTable;

DROP Table TransTable;