Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys!
I've difficult task, I need to load data from the files where data located not normally. Data located in blocks with three lines. please see example below:
Sales Cost | |
Upload Cost | |
Sellthru Cost |
$1,969 | |
$2,363 | |
83.3% | |
$4,348 | |
$5,591 | |
77.8% | |
$4,486 | |
$6,334 | |
70.8% | |
What should I use in this case I've never loaded data from such files.
source:
load * inline [
field
Sales Cost
Upload Cost
Sellthru Cost
$1,969
$2,363
83.30%
$4,348
$5,591
77.80%
$4,486
$6,334
70.80%
] (delimiter is '|');
set lj= ' ';
for i=1 to 3
let fieldname=FieldValue('field', $(i));
final:
$(lj) load
field as [$(fieldname)],
div(recno()-1,3) as id
Resident source
where div(recno()-1,3) > 0 and mod(recno()-1,3)+1=$(i) ;
set lj= ' left join ';
next;
DROP table source;
DROP Field id,
just to confirm your data is all aligned in one single row and looks like this
Sales Cost | |
Upload Cost | |
Sellthru Cost | |
$1,969 | |
$2,363 | |
83.30% | |
$4,348 | |
$5,591 | |
77.80% | |
$4,486 | |
$6,334 | |
70.80% |
right ?
What is the data source? Is there spaces between rows?
You are right
Please see screen from file
Data without spaces between rows
can you share an example of the whole data set, since it seems as one row is not going to do the trick
Numbers in table shifted by one row
Hello, I think this works, try it
Origen:
load * inline
[Campo,
'Sales Cost',
'Upload Cost',
'Sellthru Cost',
'$1,969',
'$2,363',
'83.3%',
'$4,348',
'$5,591',
'77.8%'
];
Tabla:
LOAD
Campo,
if(mod(Recno(),3)=0, '[Sellthru Cost]',
if(mod(Recno(),3)=1, '[Sales Cost]','[Upload Cost]'))
as orden,
ceil(RowNo()/3) as id
resident Origen
where recno() > 3;
drop table Origen;
TablaOk:
load id, Campo as [Sales Cost] resident Tabla where orden = '[Sales Cost]';
left join
load id, Campo as [Upload Cost] resident Tabla where orden = '[Upload Cost]';
left join
load id, Campo as [Sellthru Cost] resident Tabla where orden = '[Sellthru Cost]';
drop field id;
drop table Tabla;
this will give you the first row of data, if you could share the data or just the correct layout I could probably provide a better solution
Temp:
load
if(Peek(Seq) = 3, 1,RangeSum(peek(Seq),1)) as Seq,
A,
1 as C ,
RowNo() as Row;
Directory;
LOAD A
FROM
Book1.xlsx
(ooxml, no labels, table is Sheet1)
where isnull(A) =0;
store Temp into myfile.txt (txt);
Lables:
load
A as Labels
Resident Temp where Row <= 3;
for Labelcounter = 1 to 3
let vLabelName = FieldValue('Labels',Labelcounter);
if isnull(TableNumber('Table')) then
Table:
load
A as [$(vLabelName)],
Row-Seq as Seq
Resident Temp
where Seq = '$(Labelcounter)' and Row > 3;
else
join(Table)
load
A as [$(vLabelName)],
Row-Seq as Seq
Resident Temp
where Seq = '$(Labelcounter)' and Row > 3;
end if
next
drop tables Temp,Lables;
source:
load * inline [
field
Sales Cost
Upload Cost
Sellthru Cost
$1,969
$2,363
83.30%
$4,348
$5,591
77.80%
$4,486
$6,334
70.80%
] (delimiter is '|');
set lj= ' ';
for i=1 to 3
let fieldname=FieldValue('field', $(i));
final:
$(lj) load
field as [$(fieldname)],
div(recno()-1,3) as id
Resident source
where div(recno()-1,3) > 0 and mod(recno()-1,3)+1=$(i) ;
set lj= ' left join ';
next;
DROP table source;
DROP Field id,