Apply parent header to children line items

[#1]

Hi all,

I have a table similar to that shown below. I have an account number followed by several lines of data. I'd like to add a field for each line of data that includes the account to which it corresponds. Is there a way to reach in an account name and apply it to all subsequent line items until another account name is found? The account name is easy to find because each is prefixed by a shared string (ex. "ACCOUNT NAME:").

Account1:	ABCD
	Data1
	Data2
	...
EndAccount1
Account2:	1234
	Data3
	Data4
	....
EndAccount2

Desired:

Header 1	Header 2
Account1	Data1
Account1	Data2
Account2	Data3
Account2	Data4

[swuehl]

I think it would be easier if you could post a sample of your real data structure, your above sample structure looks a bit unclear to me.

I think you can achieve what you want using peek() or previous() functions, like

Test:

LOAD if(Account like 'Account Name*', Account, peek(Account)) as Account, RecNo, Value;

LOAD recno() as RecNo, * INLINE [

Account, Value

Account Name: Account1,

, Data1

, Data2

Account Name: Account2,

,Data3

,Data4

];

// Remove header lines

Result:

LOAD Account, Value Resident Test Where len(trim(Value));

drop table Test;

[whiteline]

Hi.

Use the load transformation step.

You can easily fill empty cells with the value above.

[#4]

In what way do you mean? I've only used cross tables - is that what you're referring to?

[#5]

Yea, sorry about the vague sample data. Maybe this will help.

I want the numbers highlighted in red and bolded. I've already figured out a way around the header and footer of each section, so that's no problem. I simply want the account number to associate with each number.

[maxgro]

Source:

LOAD * INLINE [

Field

ACCOUNT NAME: ABCD

Data1

Data2

Data3

Data4

Data5

EndAccount1

ACCOUNT NAME: 1234

Data3

Data4

Data12

Data13

EndAccount2

];

Table:

NoConcatenate load

if (wildmatch(Field, 'ACCOUNT NAME:*'),

     trim(replace(Field, 'ACCOUNT NAME:', '')), peek(Account)) as Account,

Field as Field

Resident Source

where not wildmatch(Field, 'EndAccount*');

Final:

NoConcatenate LOAD * resident Table

where left(Field, 13) <> 'ACCOUNT NAME:';

drop table Source;

DROP Table Table;

[whiteline]

Open File Wizard, Click Next and then click 'Enable Transformation Step'.

Then use appropriate operations to conditionally remove unnecessary rows and fill account data for each row.
The result is strongly depends on your data structure. Try to use flexible constraints so that it works for a few additional columns or rows.

For example (the filter statement is created by the wizard, you don't have to write it manually):

LOAD [Number/Name],

  Description,

  [Monthly Spend],

  Equipment,

  Tax,

  Totals

FROM

DataSample.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Compound,

RowCnd(CellValue, 2, StrCnd(null)),

RowCnd(CellValue, 1, StrCnd(start, 'Account:', not)),

RowCnd(CellValue, 1, StrCnd(start, 'Number/Name', not))

)),

Replace(1, top, StrCnd(start, 'Account:', not)),

Remove(Row, RowCnd(CellValue, 3, StrCnd(null)))

));