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

[#1]

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

[ramoncova06]

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

[Anonymous]

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]

[#4]

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

the problem is with the branch.

[hic]

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

[#6]

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

[hic · Accepted solution]

// ================= 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);

[#8]

thanks a lot Henric