I am new to this platform and i am facing an issue so need help. My problem scenario is I have File A which has multiple rows in one column and these rows are actually headings which will be used as a column for extracting column details from file B. For example:
File A
Company Name (this is column heading )
CapIQ Excel ID
Ticker
Description
HQ (City)
HQ (State)
HQ (City)
File B
Company Name CapIQ Excel ID Business Description Revenue EBITDA EBIT HQ (City)
Company A IQ1919 xxxxxxxxxx $700 $500 $350 US
I want to create a new file in which the column names will be of FILE A and the data will be extract from FILE B only for column mentioned in FILE A.
How can this be achieved? Any help?
Solved! Go to Solution.
Hi,
you could do this by doing the following:
(the top file includes the data in the column, the bottom is the "lookup" file.
You could:
1. Use the data cleanse to make sure there are no unnecessary characters in the rows (such as leading/trailing spaces)
2. Add a record ID
3. Cross tab the column using the following configuration:
where field 1 is the column that contains the headers
4. Use the dynamic rename to get rid of the _ separators in column names (the corsstab tool produces the column names with spaces replaced by _. The reverse this, use the dynamic rename with the following expression:
replace([_CurrentField_],"_"," ")
4. Use the sample to skip 1st row.
5. Union the files by name
Please find the flow attached - let me know whether this works for you.
Alternatively, for a simpler solution, use the make columns tool:
and a dynamic rename set to take field names from first row of data.
Thank you very much for the solution. Now i will apply union on File A and File B to extract the relevant column data.
Using the workflow financial data like Revenue, EBITDA and EBIT are showing under Description, HQ City, HQ State and US country under ticker which is incorrect.