Getting Started with Transformation

[Olaf]

You need just to go through the activities you do when you do this massive task manually, don’t let you block as you think you are not a technical person. EDT just helps you to klick to gather the tasks you are doing manually, so that in future they are done automated in seconds.

I executed some steps but not all of them, but it should give you an idea, so that you can complete it own your own. (in case you want to add transformations in between existing tasks make the connection arrow and than you select the transformation to be added). For specific topics you can ask.

What is done in the example:

First row: Data is read, ignored rows are removed and existing columns are renamed to target name

Second row: All Rows not existing with fixed values are set. The currency is added and the excange rate are taken form the exchange rate file with the current valid rates. (as I suggested before).

Then the Format Hardcover, etc is set (you need to check the mapping values) Further replace arguments can be added.

Split of the columns “PTD_avg_list_price” with 2 values is done. If I understand your explanation of the input data correctly. Resulting fields are renamed

Was a Return? Field is calculated

Unit Offer Price Local Currency is calculated, but I’m not sure if this is the intended transaction

Total Sale Local Currency calculated
A bunch of other calculations I did not executed, but the above should give you an idea how to proceed.

Unit Offer Price USD calculated. And afterwards start the little “trick” @Admin mentioned before.
The data set is now take unchanged and the Return QTY is set to NULL. In the second breakout only the records with a Return QTY > 0 are taken. For these the QTY sold is set to NULL. In three following steps the negative value for “Total Royalty Amount USD” is calculated and the interim column removed. Afterwards the to tables are merged again. And the duplicated rows are included. You need to check if the result insufficient in case all books are returned or if a further logic is required.

This should give you all ideas and help to complete on your own and write the result into a Excel file.

books 3.transform (20.1 KB)
Books_Input.csv (2.4 KB)
exchange rates.txt (27 Bytes)

Don’t forget to add notes as much as possible, that you don’t lose track on your logic later on and make use of the Comment field in the transformations:

It might be some effort to do the first implementation for all formats but in the long turn it will save a lot of time.

[Olaf]

two further remarks.

If you are uncertain with some transformations use the “?” to get the help, which is excellent (thanks @Admin for this).

In difference to @Monotone94 I keep the exchange rate in a separate file which can be updated daily, weekly or whatever. I’m not a fan to change an existing tested working transformation file (even if it is a clipboard). Through this the “.transformation” file can stay untouched. Therefore I suggest to use for the input file the same name for each execution. When you are familiar with the process and RDT there are more alternatives to process multiple files in one run with different name…

[Monotone94]

@Olaf

In difference to @Monotone94 I keep the exchange rate in a separate file

As I suggested in my comments within that particular transform, Olaf. For demonstration, clipboard is easier because data is included.

[RGYP]

I can see how the comment about two rows might be confusing. Believe me, it is for me, and I’ve been doing it manually.

A row for the example report received contains both sales data for the period and book return data for the period.

When a book has both sales and returns in the same period, I manually take the one row and create two rows with the data. One row for the sales of that book title and one row for the returns of that book title. When it’s a return, today, I manually transform numbers to negatives. Essentially, one row from the source becomes two in the import sheet.

So, using QTY Sold as an example, if in a row, if a book sold 20 copies and then under the returns column, 5 were returned. In the sheet I manually created for import, there is a row that shows 20 in QTY Sold, and a second row that I added that has -5 in QTY Sold.

[Olaf]

My example considers the row copy, only logic needs a change as it considers the return count as a positive number and not a negative number. An the calculation step multiply be -1 can be removed, too.

[Monotone94]

@RGYP You have a framework from me and a substantial example from @Olaf. At this point I presume you are working on the final transforms you need for your three cases. If you run into difficulties, ask again. The more you use EDT, the more you discover useful things you had not realised it could do. Your task is exercising some basic elements; there is much more in the product if you need it.

[Anonymous]

@RGYP

I receive 3 different CSV files. One of the distributors sends a separate file for each currency: USD, GBP, and AUD. One of them mixes the currencies in the same file, and the last one sends a file, and the values have already been converted to USD by them.

Is it possible for you to provide the sample for each file and the import file you are creating for each manually.

Just couple of records for each file that cover scenarios that you have mentioned.

Sales and Returns

Sales and No Returns

No Sales and Returns

If the information is sensitive and you don’t like to share, then see if you can modify the following records that I have created, to make it realistic to your environment and provide the import file so that it is clear what goes where.

publisher_number	publisher_name	isbn	sku	parent_isbn	title	author	page_count	binding_type	book_type_id	list_price	wholesale_discount_%	PTD_Quantity	PTD_avg_list_price	PTD_extended_list	PTD_avg_discount_%	PTD_extended_discount	PTD_avg_wholesale_price	PTD_extended_wholesale	PTD_avg_print_charge	PTD_extended_print_charge	PTD_gross_pub_comp	PTD_extended_adjustments	PTD_extended_recovery	PTD_pub_comp	YTD_quantity	YTD_avg_list_price	YTD_extended_list_price	YTD_avg_discount_%	YTD_extended_discount	YTD_avg_wholesale_price	YTD_extended_wholesale	YTD_avg_print_charge	YTD_extended_print_charge	YTD_gross_pub_comp	YTD_extended_adjustments	YTD_extended_recovery	YTD_pub_comp	deferral_balance	reporting_currency_code	period_name	original_deferral_amount	PTD_return_quantity	PTD_return_wholesale	PTD_return_charge	PTD_return_total	YTD_return_quantity	YTD_return_wholesale	YTD_return_charge	YTD_return_total	PTD_net_quantity	PTD_net_wholesale	PTD_net_pub_comp	YTD_net_quantity	YTD_net_wholesale	YTD_net_pub_comp	returns_flag_value	nonreturnable_date	title_status_flag_value	cancelled_date	publisher_imprint	customer_flexfield1	customer_flexfield2	customer_flexfield3	customer_flexfield4	customer_flexfield5	isbn_13	PTD_wholesale_tax	PTD_print_charge_tax	PTD_return_wholesale_tax	PTD_return_charge_tax	YTD_wholesale_tax	YTD_print_charge_tax	YTD_return_wholesale_tax	YTD_return_charge_tax	market	sales_category	Ptd_global_distribution_fee
101	Alpha Publishing	9781234567890	SKU001	9781234567850	The Silent Echo	Emily Carter	320	Trade Cloth/Laminate	1	24.99	40	150	24.99	3748.50	40	1499.40	14.99	2248.50	2.50	375.00	1873.50	0.00	0.00	1873.50	500	24.99	12495.00	40	4998.00	14.99	7495.00	2.50	1250.00	6245.00	0.00	0.00	6245.00	0.00	USD	Q1-2025	0.00	5	74.95	12.50	87.45	15	224.85	37.50	262.35	145	2173.55	1786.05	485	7270.15	5982.65	N	2023-12-31	A		Alpha Fiction						978-1234567890	134.91	18.75	4.50	0.75	449.70	62.50	13.50	2.25	US	Trade	15.00
102	Beta Books	9782345678901	SKU002	9782345678961	Data Science 101	David Lin	450	Perfectbound (Trade Paper)	2	49.99	35	80	49.99	3999.20	35	1399.72	32.49	2599.20	5.00	400.00	2199.20	0.00	0.00	2199.20	300	49.99	14997.00	35	5248.95	32.49	9747.00	5.00	1500.00	8247.00	0.00	0.00	8247.00	0.00	USD	Q1-2025	0.00	0	64.98	10.00	74.98	10	324.90	50.00	374.90	78	2534.22	2124.22	290	9422.10	7872.10	N	2023-11-15	A		Beta Academic						978-2345678901	116.96	20.00	2.60	0.50	584.80	100.00	13.00	2.50	EU	Education	20.00
103	Gamma Press	9783456789012	SKU003	9783456789005	Advanced Calculus	Robert Hale	600	Trade Cloth/Laminate	2	89.99	30	0	89.99	4049.55	30	1214.87	62.99	2834.55	8.00	360.00	2474.55	0.00	0.00	2474.55	180	89.99	16198.20	30	4859.46	62.99	11338.20	8.00	1440.00	9898.20	0.00	0.00	9898.20	0.00	GBP	Q1-2025	0.00	1	62.99	8.00	70.99	3	188.97	24.00	212.97	44	2771.54	2403.54	177	11149.23	9685.23	Y	2023-10-01	I	2023-09-10	Gamma Academic						978-3456789012	141.77	18.00	3.15	0.40	425.31	54.00	9.45	1.20	UK	Academic	25.00
104	Delta Media	9784567890123	SKU004	9784567890173	The Last Horizon	Sarah Kim	280	Perfectbound (Trade Paper)	1	14.99	50	200	14.99	2998.00	50	1499.00	7.50	1500.00	1.50	300.00	1200.00	0.00	0.00	1200.00	800	14.99	11992.00	50	5996.00	7.50	6000.00	1.50	1200.00	4800.00	0.00	0.00	4800.00	0.00	USD	Q1-2025	0.00	10	75.00	15.00	90.00	40	300.00	60.00	360.00	190	1425.00	1110.00	760	5700.00	4440.00	N		A		Delta Fiction						978-4567890123	75.00	7.50	3.75	0.75	300.00	30.00	15.00	3.00	US	Mass Market	10.00
105	Epsilon House	9785678901234	SKU005	9785678901284	Cookbook Delights	Julia Park	400	Trade Cloth/Laminate	3	29.99	45	120	29.99	3598.80	45	1619.46	16.49	1978.80	3.00	360.00	1618.80	0.00	0.00	1618.80	500	29.99	14995.00	45	6747.75	16.49	8245.00	3.00	1500.00	6745.00	0.00	0.00	6745.00	0.00	USD	Q1-2025	0.00	0	65.96	12.00	77.96	20	329.80	60.00	389.80	116	1912.84	1540.84	480	7915.20	6355.20	N	2023-12-01	A		Epsilon Lifestyle						978-5678901234	98.93	18.00	2.64	0.60	494.65	90.00	13.20	3.00	CA	Non-Fiction	12.00

Here are the field mappings that I did, but it is not clear to come up with a solution, so need sample for Input and desired output.

Import Fields	File Fields	Comments	Notes
ISBN	parent_isbn	Direct mapped
Title	title	Direct mapped
Sales Source		Add column	Empty for file1
Author Copy?		Add column	For file1 one value = No
Type of Order		Add column	For file1 one value = Regular
Order Number		Add column	Empty for file1
Invoice Number		Add column	Empty for file1
Retailer		Add column	Empty for file1
Format	binding_type	Mentioned three but provided only two formats	There are three binding types. “Perfectbound (Trade Paper)” then filled with “Paperback”; If Trade Cloth/Laminate then filled with Hardcover
QTY Sold	PTD_Quantity	Direct mapped
Average List Price	PTD_avg_list_price	This is the first column	This is mapped to two fields in the import file
Unit Offer Price Local Currency	PTD_avg_list_price	This is the second column
Was a Return?	PTD_return_quantity	Derived Column	If there is data populated in the PTD_return_quantity, then this field si filled with “Yes” if not, it is filled with “No”
Discount Given		Instead of Emply put zero as it is used in calculation for other fields	Empty for file1
Currency Code		Need sample	For this file type this field is null in the import file and because I “know” what the currency is, I fill it manually. It is mapped for other file types received
Exchange Rate		Need sample	This is manually filled in the import sheet
Total Sale Local Currency	Average List Price * Qty Sold	calculated field for import file	This is a calculated value of Average List Price * Qty Sold
Discount Amount Per Unit Local Currency	Average List Price * Discount Given	calculated field for import file	This is a calculated value of Average List Price * Discount Given, if there is no discount, this is filled with zero
Unit Offer Price After Discount Local Currency	Average List Price - Discount Amount Per Unit Local Currency	calculated field for import file	This is a calculated value of Average List Price - Discount Discount Amount Per Unit Local Currency
Total Sale After Discount Local Currency	Unit Offer Price After Discount Local Currency * QTY Sold	calculated field for import file	This is a calculated value of Unit Offer Price After Discount Local Currency * - QTY Sold
Unit Offer Price USD	Average List Price (*,/) USD rate	calculated field for import file	This is a calculated value of Average List Price and Unit Price Local Currency converted to USD using Exchange rate
Total Sale USD Before Discount	Unit Offer Price USD * QTY Sold	calculated field for import file	This is a calculated value of Unit Offer Price USD * QTY Sold
Discount Amount Per Unit USD	Discount Amount Per Unit Local Currency (*,/) USD rate	calculated field for import file	This is a calculated value of Discount Amount Per Unit Local Currency converted to USD using Exchange rate
Unit Offer Price After Discount USD	Unit Offer Price USD - Discount Amount Per Unit USD	calculated field for import file	This is a calculated value of Unit Offer Price USD - Dicount Amount Per Unit USD
Total Sale After Discount USD	Unit Offer Price After Discount USD * QTY Sold	calculated field for import file	Unit Offer Price After Discount USD * QTY Sold
Print Manufacturing and Delivery Charge	PTD_extended_print_charge	calculated field for import file
Total Royalty Local Currency	PTD_pub_comp	calculated field for import file
Total Royalty Amount USD	Total Royalty Local Currency (*,/) USD rate	calculated field for import file	This is a calculated value of Totally Royalt Local Currency converted to USD using the exchange rate
Notes	Notes	Need sample	For this file type this field is null in the import file and because I “know” what the currency is, I fill it manually. It is mapped for other file types received
Qty Returned	PTD_return_quantity	Need sample	SPECIAL HANDLING. If the quantity returned is NOT Null, then TWO rows are created in the import sheet. One row for the books sold and one row for the books returned.
Total Royalty Amount USD	PTD_return_total	Need sample. Also Import Field name is doubtful because it is in USD	SPECIAL HANDLING. If the quantity returned is NOT Null, then TWO rows are created in the import sheet. This field converted to a negative value and mapped to both Total Sale After Discount Local Currency and Total Royalty Local Currency
Marketplace	market	Need sample