Link Customer to Pricelist

[johngouws]

Good morning. 
I'm sitting with a scenario where I need to show the Price of Items per Customer, based on their Pricelist number. There are 5 pricelists available, numbers 1 to 5. On the Item side there are 5 pricelists fields, PL1 to PL5. There is no common field between the tables for association. The attached snippet shows it better. 

The fields in the two tables are.

Item:
SQL SELECT
ItemCode,
PL1,
PL2,
PL3,
PL4,
PL5
FROM Items;

Customer:
SQL SELECT
CustomerCode,
"Price List"
FROM Customer;

I really appreciate any thoughts on how this can be done and if it actually even possible. I include a simplefied version of the model and spreadsheet. In reality there are about 14000 Customers and 9000 Items

Thank you in advance. 

[rwunderlich · Accepted solution]

Thanks for providing such complete example files, it makes it easy to answer.

You just need to CrossTable Load your Item table.

Items:
CrossTable([Price List], Price, 1)
;
SQL SELECT
ItemCode,
PL1,
PL2,
PL3,
PL4,
PL5
FROM Items;

Then you will need to make the 'PL1' code align with the "1" code in Customer."Price List". In the SQL something like:

Concat('PL',"Price List") as "Price List"

Here's my solution in your QVW script:

Items:
CrossTable([Price List], Price, 1)
LOAD ItemCode,
PL1,
PL2,
PL3,
PL4,
PL5
FROM
[CustomerPricelist.xlsx]
(ooxml, embedded labels, table is Item);

Customers:
LOAD CustomerCode,
'PL' & [Price List] as [Price List]
FROM
[CustomerPricelist.xlsx]
(ooxml, embedded labels, table is Customer);

-Rob

[johngouws]

Thank you very much Rob. I've been struggling but cross table did not come to mind.. 
Your solution works perfectly. 
In the live environment I went for doing the concat under the LOAD rather than the Pervasive Sql. The performance is better. 

Thanks, John

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download