topic Re: conditional values..Pls help ....itssssssssss urgentttttttt in QlikView

conditional values..Pls help ....itssssssssss urgentttttttt

Tue, 09 Jun 2015 17:16:20 GMT

Hi Experts,

I have the following qvds

Connect:

LOAD BRCODE,

CUSTNO,

FROM

Customer List.xls

CUSTOMER:

LOAD CUSTNO,

CUSTNAME

FROM

[CUSTOMER.qvd]

(qvd)

where Exists(CUSTNO);

BRANCH:

LOAD BRCODE,

BRNAME

FROM

[BRANCH.qvd]

(qvd);

where Exists(BRCODE);

SALES:

LOAD BRCODE,

Date,

CUSTNO,

Amount

FROM

[\QVDs\SALES.qvd]

(qvd)

where Exists(CUSTNO)and Exists(BRCODE);

My scenario as follows:

the connect table has some limited branches with customer nos allocated to it, for ex it has 100 branches. each branch having 10 customers nos.

the customer table bringsup the customer name of the customer nos which exists in connect table.

the branch table bringsup the branch name of the BRCODE which exists in connect table.

the Sales table bringsup the sales records of the BRCODE,CUSTNO which exists in connect table.

now the problem here is, there are some customers which does sales in some other branches other than the allocated branches.

Ex., the connect table has

BRCODE CUSTNO

01 a000789

01 d666894

02 e000120

02 i5962227

03 k8965741

and so on.

Lets consider CUSTNO e000120,

actually, this e000120 is associated with BRCODE 02 in connect table. but the sales has done in 02 and 04 as well.

My requirement is, in a pivot table,

i have to bring like

BranchName(as per connect table) customer no(as per connect table) sales amount(sales value in all the branches)

ex. 02 e000120 540(sales value in branch 02 &04)

How can i do this. pls help.

As of now i m getting like

BranchName(as per connect table) customer no(as per connect table) sales amount(sales value in all the branches)

ex. 02 e000120 220

04 e000120 320

which is not the requirement.

how can i do the one highlighted in green

Re: conditional values..Pls help ....itssssssssss urgentttttttt

ramoncova06 — Tue, 09 Jun 2015 17:22:30 GMT

I would recommend getting rid of the connect table since it seems as yous sales table can act as your fact, I might be missing something, but to me it just seems a little redundant

Re: conditional values..Pls help ....itssssssssss urgentttttttt

Anonymous — Tue, 09 Jun 2015 17:44:32 GMT

Maybe have 2 "Customer" dimensions.

a) [Sales Customer] from Sales table

b) [Allocated Customer] from Connect table

Personally I would use ApplyMap() 's and create a single Fact Table. Others may go for a Star Schema around a Facts table with 3 dimensions :

[Branch]

[Sales Customer]

[Allocated Customer]

Re: conditional values..Pls help ....itssssssssss urgentttttttt

Tue, 09 Jun 2015 18:27:07 GMT

can u please help me with the script......

the problem is with the branch.

Re: conditional values..Pls help ....itssssssssss urgentttttttt

hic — Wed, 10 Jun 2015 05:46:50 GMT

As I see it, you have two different branches: Allocated branch and Sales branch. These should not be linked.

In other words: You need to load these two branches under different names.

In the Connect table: BRCODE as AllocatedBRCODE

In the Sales table: BRCODE as SalesBRCODE

In addition, you need to load the Branch table twice, using different field names.

This is very similar to how the circular reference is broken in Circular References

HIC

Re: conditional values..Pls help ....itssssssssss urgentttttttt

Wed, 10 Jun 2015 09:28:46 GMT

Hi Henric,

Can you please provide me the script.

I tried as per your suggestions, but not getting the requirement.

looking forward to your reply

Re: conditional values..Pls help ....itssssssssss urgentttttttt

hic — Wed, 10 Jun 2015 09:51:54 GMT

// ================= Allocated Branches
Allocations:
LOAD CUSTNO,
BRCODE as AllocatedBRCODE
FROM Customer List.xls (...);

AllocatedBRANCH:
LOAD
BRCODE as AllocatedBRCODE,
BRNAME as AllocatedBranch
FROM [BRANCH.qvd] (qvd)
where Exists(AllocatedBRCODE,BRCODE);

// ================= Customers
CUSTOMER:
LOAD CUSTNO, CUSTNAME
FROM [CUSTOMER.qvd] (qvd)
where Exists(CUSTNO);

// ================= Facts
SALES:
LOAD Date, CUSTNO, Amount,
BRCODE as SalesBRCODE
FROM [\QVDs\SALES.qvd] (qvd)
where Exists(CUSTNO);

// ================= Sales Branches
SalesBRANCH:
LOAD
BRCODE as SalesBRCODE,
BRNAME as SalesBranch
FROM [BRANCH.qvd] (qvd)
where Exists(SalesBRCODE,BRCODE);

Re: conditional values..Pls help ....itssssssssss urgentttttttt

Wed, 10 Jun 2015 11:27:10 GMT

thanks a lot Henric