How do you extract or select a date from a column header?

helenh

For an ETL dataset that I want to update weekly, the date and time period (e.g., "Period : Latest 4 Wks - W/E 09/07/19...") is in the first cell of the Excel file.   When I create the initial dataset (via Box), this field comes in as a column header.  Since it is not a unique column name each week (because of the changing date), in ETL I can't automate selecting this column to collapse it or to split it.  Ultimately, I want to create two constant columns, one column for "Latest 4 Weeks" and one column for the date "09/07/19".  How do I push this column header into a row so I can extract these parts of the string?

 

 

ST_Superman

Good news and bad news for you @helenh 

 

There is a solution to this problem.  However, you are going to have to use some MySQL to do it.  I will show you how to set it up.  I am just going to bring in the header of the first cell into a column.  Then you can put this dataset into and ETL and make any changes you need to.

 

I had to make an assumption that essentially cell A1 contained this "Period and W/E" field in it and that your data table really started in cell B1.

 

This is a preview of my starting dataset:

You will need to create a new MySQL dataflow and use your dataset as the input file.  (I named my dataset 'dojo_helenh' so you will need to replace that with the name of your dataset)

 

step 1 (a table transform):

SELECT
	GROUP_CONCAT(
      CONCAT(
        'SELECT ',QUOTE(COLUMN_NAME),' as `Period and Wk`, `Field 1`, `Field 2` FROM dojo_helenh') 
      SEPARATOR ' UNION ALL ') as transpose_statement
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='dojo_helenh'
AND COLUMN_NAME NOT IN ('Field 1','Field 2');

You will also need to replace the `Field 1`, `Field 2`, [`Any additional fields`] in both lines where they are used.  You will notice that the first time they are used it is with the single back tick ` and when they are called in the WHERE clause (last line), you use a single quote '

 

When you create this transform, there is a spot to name the table it is creating in the upper left corner:

 

 

Next step (this is a SQL transform)

CREATE PROCEDURE transpose()

BEGIN

SELECT `transpose_statement` INTO @sql FROM `step_1`;

SET @str=CONCAT('create table new_table as ',@sql);

PREPARE q FROM @str;
EXECUTE q;

END

Next step (another SQL transform):

call transpose;

Then you can add your output dataset:

SELECT * FROM new_table

 

Use this output dataset in your ETL and you will have the values of that first cell in a field called `Period and Wk`