Load 1st row as Header Product name and second row as Data header + Data

[abisson1]

Hello,

I have file which look like this

Product Name1

SP1    SP2    SP3 ...

100     150     215                   

Product Name2

SP1    SP2    SP3 ...

215     174     157    

The goal is to load SPx data and associate it to the product name, so it should look like this

                           SP1    SP2    SP3 ... 

Product Name1     100     150     215 ...

Product Name2     215     174     157 ...

I am capable to load all SPx data but cannot associate it the Product Name which 1 row above.

Also to complicate a bit, I need to load multiple txt file because each day a file is created with the product built in that day.

Your help will be appreciated.

Andre

[marcus_sommer]

I think I would use a loop which loads only those record which have a content and would transform them with The Crosstable Load‌. I mean something like this:

Counter:

LOAD [@1:1]

FROM [..\Community\Dec 02 2016 data.txt] (fix, utf8);

for i = 7 to noofrows('Counter') step 8

    table:

    first 1 load * FROM

    [..\Dec 02 2016 data.txt] (txt, utf8, embedded labels, delimiter is '\t', header is $(i) lines);

next

final:

crosstable(Category, Value, 2) load * resident table;

drop table Counter, table;

- Marcus

[abisson1]

Hello Marcus,

Thanks for the quick answer, but it does not provide the expected result.

Maybe I did not well explain my problem.

The file contain a lot of line that will be populated each 5 min with equipment setting. The data is regroup in consequetive 3 row and is explain below:

Product name is on 1st row --> only information on this row

Data Header is on 2nd row --> Multiple data header more than 50

Actual Data is on 3rd row --> Multiple data

What I will like to achieve is to associate the 1st row with row 2 and row 3 and so forth for each other group.

The end result should look like:

Product Name (from row 1) --> Data Header 1 (from row 2) Data Header 2 (from row 2) …etc

Product Name (from row 1) --> Data 1 (from row 3) Data 2 (from row 3) … etc

And perform same action on all other set of 3 row.

PS (maybe first row of each file is blank, so if start at row 2 than the group will 2,3 and 4)

PS2, linking row 2 and 3 is easy but I don’t know how to link Row1 with 2 and 3

Bonne journée / Best Regards,

André Bisson <mailto:%20andre.bisson@miranda.com> | PCBA Process/ Project Specialist - Manufacturing Engineering

Grass Valley, A Belden Brand<http://new.grassvalley.com/> | Cel: (438) 998-2177 - Tel: (514) 333-1772 Ext: 3020

3499 Douglas-B.-Floreani, Montreal, Quebec, Canada, H4S 2C6

[marcus_sommer]

Now with Product Name (if I had understand the datastructure right):

Counter:

LOAD [@1:n] as Record, recno() as RecNo

FROM [..\Dec 02 2016 data.txt] (fix, utf8);

for i = 7 to noofrows('Counter') step 8

    table:

    first 1

    load

          if(peek('Record', $(i) - 6, 'Counter') = 'Cooldown', peek('Product Name'),

               peek('Record', $(i) - 6, 'Counter')) as [Product Name], *

    FROM [..\Dec 02 2016 data.txt]

    (txt, utf8, embedded labels, delimiter is '\t', header is $(i) lines);

next

final:

crosstable(Category, Value, 3) load * resident table;

drop table Counter, table;

- Marcus

[abisson1]

Hello Marcus,

Thanks again for all the help.

If I keep for i=7 … than I get the “Field name must be unique withing table”

If I change for i=4 … than I don’t get any error, but each line are not associated to the product name. What am’I doing wrong?

Maybe I did not mention it, but I’m using Sense Desktop.

Regards,

Bonne journée / Best Regards,

André Bisson <mailto:%20andre.bisson@miranda.com> | PCBA Process/ Project Specialist - Manufacturing Engineering

Grass Valley, A Belden Brand<http://new.grassvalley.com/> | Cel: (438) 998-2177 - Tel: (514) 333-1772 Ext: 3020

3499 Douglas-B.-Floreani, Montreal, Quebec, Canada, H4S 2C6

[marcus_sommer]

Do you use exactly the same script from above with the same file?

- Marcus

[abisson1]

Hello Marcus,

Yes, here’s the complete error

Counter << Dec 02 2016 data Lines fetched: 2,304

The following error occurred:

Field names must be unique within table

The error occurred here:

table: first 1 load if(peek('Record', 7 - 6, 'Counter') = 'Cooldown', peek('Product Name'), peek('Record', 7 - 6, 'Counter')) as , * FROM (txt, utf8, embedded labels, delimiter is '\t', header is 7 lines)

Data has not been loaded. Please correct the error and try loading again.

Regards,

Bonne journée / Best Regards,

André Bisson <mailto:%20andre.bisson@miranda.com> | Chef de groupe, Ingénierie de fabrication / Group Leader, Manufacturing Engineering

Grass Valley, A Belden Brand<http://new.grassvalley.com/> | Tel: (514) 333-1772 Ext: 3020

3499 Douglas-B.-Floreani, Montreal, Quebec, Canada, H4S 2C6

[marcus_sommer]

You missed a fieldname here:

The error occurred here:

table: first 1 load if(peek('Record', 7 - 6, 'Counter') = 'Cooldown', peek('Product Name'), peek('Record', 7 - 6, 'Counter')) as [Product Name], * FROM  (txt, utf8, embedded labels, delimiter is '\t', header is 7 lines)

Data has not been loaded. Please correct the error and try loading again.

- Marcus