Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
Do you use exactly the same script from above with the same file?
- Marcus
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
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