Extract data from table shuffled at different row number

[bubblyjai1982]

Hi All

Need your assistance !!

Using dynamic input tool I have extracted multiple files but facing a challenge in extracting the data available at different row and column.

Example from tab file 1

Tsec: PTXHKD00DBEPL01

Total: 550

Similar I want to extract same field from other files - The challenge is the table available in different files are not at same row number.

Input and output data attached for your reference.

Thanks

[Pilsner]

Hello @bubblyjai1982,

I've tried to tackle your problem, please see my method below.

1) I tried to follow your initial step of reading in the data using the dynamic input tool.

2) Then I created a new column to identify each sheet separately. To do this, I used a multirow. Looking at the data, I could see that Total was always the last record for each sheet; hence, I used this to identify the end/ beginning of each sheet. 

3) From each sheet, we want the total and TSec records, hence I used filters to identify these records

4) Now I had the total and Tsec for each sheet, I joined them back on to each other, using the "Sheet" column I created earlier. 

5) Finally, some of the sheets mentioned the TSec code twice so I used a summarise tool, to deduplicate the data. 

I have attached the workflow below. Please let me know if you have any further questions.

Regards - Pilsner

[bubblyjai1982]

Thanks for the response Pilsner !!

The logic of Multi row will not work because the data is not constant, I mean the no.of files varies and its not always 3 files.

Today I had 3 files tomorrow it will differ - based on trading 

Thank you. 

[dreldrel]

Are you able to give the name range in your files? You can read data by name range. 
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Can-Alteryx-input-excel-dynami...

If this helps, please like the comment and select it as a solution. Thanks in advance

[binuacs]

@bubblyjai1982 Are File1, File2, and File3 separate tabs in a single Excel file, or are they three separate Excel files? I assumed they were tabs within one file and created the workflow accordingly. If they are individual files, then the workflow will need to be adjusted

[bubblyjai1982]

Hi Binu 

Thanks for the response !!

Actually the input files are not in same sheet.

Using Dir tool I have extracted the different files and the logic you used until filter is same as mine but only difference is I don't have column File Name.

Sorry don't want to share the data due to compliance issue, request you to please tweak your logic without file name.

Many Thanks

[binuacs · Accepted solution]

@bubblyjai1982 , for the file name, select option 5 from the dynamic input tool

[bubblyjai1982]

Hi Binu -  Thank you so much, my issue is resolved now.