Help to a newbie. Import and data handeling

Hi

Im fairly new to alteryx - only been playing around with this for a month.

Ive got a task at hand, creating a summary based on invoice specifications.

All the invoices has similar structure. But all files has more than 1 sheet.

1 Sheet that is not usefull. And 1+ Sheets with proper data.

Ive found the CReW_WildcardXLSXInput importer, which works great.

Im able to import the correct sheets, and exclude those who are not usefull.

But in each file i have:
Kundenummber (custoner number)
and
Faktura dato (invoice date)

I want to full two colums with this data.
So

File 1 has:
Invoice date: 28-11-2019
Customer number: 1

And File 2 has:
Invoice date: 01-08-2019

Customer number: 2

So i have to extract it, and fill a colum based on the file its extraced from.

Ive tried and upload my sample data.

And my attemp to create a flow (im not sure i have all files attached here)

150213004.xlsx

Postnord DK Flow Alteryx.yxmd

150213003.xlsx

Datasets

Accepted answers

RolandSchubert

The required information can be found on the first data sheet only (and on the Summary sheet you skip), but can be easily identified by the entries in cells A2 and A5. So it is possible to filter out this rows, cross tab it to create columns containing the customer and invoice number and re-join it to the data. I've modified and attached the workflow. Does this work for you?

Postnord DK Flow Alteryx modified.yxmd

All comments

vmartinez

Hi @Hamder83,

could you add the headers file?

also what you want to achieve is to extract 2 columns from each tab (invoice date and customer number) and add another column that says from which file this was extracted?

RolandSchubert

Hi @Hamder83 ,

the header file is missing, but I've already modified your workflow a bit.

Each sheet contains header rows, I've remove these using a Filter tool (NULL values in fields as condition)

There is one remaining row with the field names you use later on, but this row exists on each sheet, so I've used a second filter to identify these rows, selected the first of these rows and added the row to the data again. Using a Dynamic Rename you create the field names needed.

Next step would be select the fields you need and to do the calculations.

Hope this is helpful.

Regards

Roland

Postnord DK Flow Alteryx modified.yxmd

gautams091

In the Input Data tool, there is an option "Output File Name as Field". Select that field as "File Name Only".

File name will appear as column in the imported data file.

Regards,

Gautam

Hamder83

Hi

The headers are in the excel files, in two of the sheets as row 10 🙂

Hamder83

That seems to work like a charm! Thank you

I have one more question, and this seems super simple, but i must admit my skills aint good enough yet.

In both files there are some data i would love to have transfered into two coulms.

ive sttached the data.

But how do i create a coloumn with data from a specific cell ?

Basicly i want the flow to read the invoice number and the customer number, and fill that to each line in the file.

So i can track which lines belong to which invoice - if that makes sense?

2019_11_29_07_58_43_Window.png

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703