Partial Dimension Load

[#1]

Dear,

I have 2 tables which I'd like to link. Unfortunatly the 2 tables don't have the same info I can link.

Please see below:

Table1

Customer     Customerno.

A                    100010  

B                    200500

Table 2

Shipto               Sales

100010-01          10

100010-02          20
100010-03          30

200500-01          15

200500-02          20

200500-03          10

For the overview I want to make, I need to link the sales per customer.

Is there a way to load the the first 6 numbers info from Table 2, "shipto" (I have about 500 different customers)

I want to have the following in mij Chart:

Customer         Customerno.     Sales

A                      100010               60

B                      200500               45

thanks in advance.

Best Regards,

Sander

[tresesco · Accepted solution]

Try like:

Load

          left(Shipto,6) as Customerno

[Anonymous]

Use the left() function.

[MK_QSL]

Use below script..

=============

T1:

Load * Inline

[

  Customer, Customerno

  A,        100010 

  B,        200500

];

T2:

Load *,SubField(Shipto,'-',1) as Customerno Inline

[

  Shipto,    Sales

  100010-01, 10

  100010-02, 20

  100010-03, 30

  200500-01, 15

  200500-02, 20

  200500-03, 10

];

=============

Now Create Straight Table

Dimension

Customer

Customerno

Expression

SUM(Sales)

[#5]

thanks a lot!