If Then Else
Posted February 17th, 2010 by wbayne333
Hi Jo.
Thanks for your help in the past. Your product has saved me a ton of time.
I have another problem.
My source file has 2 columns and 500 rows. The first column is the code that corresponds to a value in the second column. ie 220|"Personal Care/Hair Care" The 500 rows are a list of codes and categories. Each row is unique
My target file has a column which includes a value = to the value in the first column of my source file.
I need to run a routine that looks at each line in the target file, matches the value in the source and write the corresponding value of the second column of source to a column in target. (Are you with me?)
I wrote this:
If Targets(0).Records("R1").Fields("subcatcode")=Fields("Field1") then
Return Fields("Field2")
End if
But it's not writing anything. There are no errors. Nothing gets written.
What do you suggest?
Thanks
Bill
Your transaction secured by high-grade AES-256 encryption.
RE: If Then Else
Hi Bill,
Glad you're finding DataTools to be a time-saver for you.
I would probably use a lookup table to accomplish what you need. For detailed documentation on using a lookup table, open the product help file and locate the help topic entitled "Lookup Function" in the "Rapid Integration Flow Language" section.
Let us know if you need further assistance.
- Jo
clueless
OK. I'm stuck.
dhcats_Field2_Lookup(KeyValue, DefaultValue)=Targets(0).Records("R1").Fields("subcatcode")
You know what I need to do. I am stuck as to what to do next. Help!
Thanks
Bill
RE: clueless
Hi Bill,
After re-reading your original post and seeing the expression in this post, I have a much clearer idea of what you're trying to do. Here's a summary...in my words...
1. Your source data file is a 2-column CSV file that is (basically) a lookup table; i.e., there is a "code" in the first field and a "description" in the second field.
2. You are trying to update the data records in an EXISTING target CSV file and insert the "code" into an existing -- presumed blank -- field.
3. The value on which you are trying to "match" the "code" to the "description" is a "description" field that exists in both your source AND your target files...and the description information matches EXACTLY between the two files.
Based on those three assumptions, here's an important question...
From #3 above, do the descriptions match EXACTLY between the source file and the existing data in the target file?
And here's a solution...IF the 3 assumptions are correct AND IF the answer to the question above is "YES":
Order a license for the Data Loader to ODBC:
http://pervasivedatatools.com/datatools/loaders/product/odbc2-uploader
You do not need to install DataTools again. Simply close DataTools (including the Launcher) when you place your order for the Loader to ODBC, wait for the license file confirmation email message, and re-start DataTools. The Launcher should prompt you to download the new license file.
Follow the instructions in the documentation for the ODBC connector (under Source and Target Connectors in the documentation) and set up an ODBC UserDSN to your TARGET CSV data file. This is done in the Administrative Tools > Data Sources (ODBC) area of the Windows Control Panel on your workstation. An ODBC driver for CSV data files ships with Windows, so there is nothing to purchase or download....just some configuration.
After the UserDSN is created, start the Data Loader to ODBC. Source Connection = ASCII (Delimited) and connect to your 2-column data file. Target Connection = ODBC and select the UserDSN connection you set up previously. Output Mode = Update File/Table and select the data fields on which to match. At the Map Fields tab, map ONLY the "description" field from the source grid to the target grid.
There are details about each of the above steps in the documentation, but let us know if you get stuck again.
- Jo
the anser to your question
the anser to your question is kinda yes.
Based on those three assumptions, here's an important question...
From #3 above, do the descriptions match EXACTLY between the source file and the existing data in the target file?
It's not the descriptions that need to match but the codes.
So if code in source file = code in target file then copy description from source to target file.
How would I write this? I have several situations where this occurs but don't know how to write it. And I need it ASAP.
Thanks
Bill
RE: the anser to your question
Hi Bill,
Based on the existence of matching codes between the source file and the target file, the solution that I suggested using the Data Loader to ODBC will work for you to update the records in the target file with "descriptions".
- Jo
Is their a tutorial or video
Is their a tutorial or video showing how to do this?
RE: Is their a tutorial or video
Hi Bill,
No, but there is a help topic in the product documentation that you will want to read. The help topic title is "Update File/Table", and you can find it in the product help file. Open "Help", go to the Search tab, and search for "Update File/Table".
- Jo
OK. Have spent the last 6
OK. Have spent the last 6 hours messing with this and getting nowhere.
My target file is getting updated with data from source file but it's not going into the rows with the corresponding skus. The data is being added at the very end of the existing data in the target file. All rows which I want to add data to are being ignored.
I'm using the dsn and ODBC 3.5 as you suggested.
Here is the scenario again.
I have a source file that has skus with related data like images, pricing and descriptions.
My target file has the same skus with sizes and colors and quantities. There are multiple rows for each sku - maybe 6-10 rows of the same sku.
The objective is to copy the images, pricing and descriptions from the source file to the corresponding skus in the target file and populate all rows for each sku.
The update file method isn't working. Maybe there is a setting(s) I'm missing. Any help would be appreciated. I'm a day behind on this project.
Thanks
Bill
RE: OK. Have spent the last 6...
Hi Bill,
Here are the basic steps...
Use the Data Loader to CSV Text
Source connection = ASCII (Delimited)
Source file = "dandhtest1.csv"
Header = True
Target connection = ASCII (Delimited)
Source file = "new_file_name.csv" (not an existing file)
Header = True
On the Map Fields tab:
a. Drag & drop the asterisk (all fields) from the source grid to the target grid.
b. Change the default field Size for the target field to which you wish to write the sub-category description to some value that you know is large enough to accommodate the longest description you may have.
c. In the target field expression cell for the target field to which you wish to write the sub-category description:
1) Click on the ellipsis to open the Expression Builder.
2) Delete the default value that was mapped to this field (probably Fields("Field2")).
3) Copy & paste the following expression into the expression builder with NO line breaks:
Lookup(Fields("subcatcode"), "C:\ILCatSub-2-columns.csv", "|")
4) Edit the path to the "ILCatSub-2-columns.csv" so it points to the folder path on your workstation where that file resides.
5) Click on OK to save that expression and to close the Expression Builder.
Please let us know if this works for you, or not.
- Jo
Great job! That worked. Love
Great job!
That worked. Love this program.
OK. I have another challenge.
The source file looks like this:
sku size
111 5
111 6
111 7
111 8
111 9
222 7
222 12
333 8
333 9
I need the data to look like this in the target file:
sku size
111 5|6|7|8|9
222 7|12
333 8|9
The sizes in the size column need to be separated by "|" pipes
Not sure which function to use for this. Thanks Bill
RE: Great job! That worked. Love...
Hi Bill,
There are no tutorials or sample maps that demonstrate exactly what you need, but there is a sample map in the support area of the Pervasive corporate web site entitled "Using Conditional Put Actions with Event Handlers" that teaches the basic technique you will need to use. Here's the URL for your convenience:
http://docs.pervasive.com/products/integration/websamples/di/samples_hel...
To access the Event Handlers in DataTools click on the "Map All" button after connecting to your source and your target.
- Jo
Hi Jo. I hate to be a pain,
Hi Jo.
I hate to be a pain, but I've spent the last day and a half going through that example you gave and couldn't get it to do what I need it to do.
To recap I need to take data from several rows and combine them into a single cell separated by "|". The script needs to look at a sku, determine how many rows it has then take the sizes and add them to the target file:
source
sku-------size
111-------6
111-------7
111-------8
222-------11
222-------13
target
sku-------size
111-------6|7|8
222-------11|13
I modified the example yet it kept giving me blank results. I also went through all of the examples in the Sample data folder and didn't see anything that could facilitate my need. The data I have has over 25000 entries so the prospect of doing this manually is time prohibited.
Any new thoughts you have would be greatly appreciated. Once we solve this I'll leave you alone for a while. :)
RE: Hi Jo. I hate to be a pain...
Hi wbayne333,
A detailed DataTools HowTo blog article is now available that will walk you through the steps/procedure needed to transpose the data file from your source format to your desired target format. Here is the title and a link to that article:
DataTools HowTo: Transpose Data from Vertical to Horizontal Orientation
http://pervasivedatatools.com/node/1108
Thank you for submitting this data challenge that resulted in a new sample transformation map for everyone in the DataTools community!
- Jo