Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vchuprina
Specialist
Specialist

How load data if it located one above the other?

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.

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
1 Solution

Accepted Solutions
maxgro
MVP
MVP

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,

1.png

View solution in original post

10 Replies
ramoncova06
Specialist III
Specialist III

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 ?

Kushal_Chawda

What is the data source? Is there spaces between rows?

vchuprina
Specialist
Specialist
Author

You are right

Please see screen from file

Untitled.png

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
vchuprina
Specialist
Specialist
Author

Data without spaces between rows

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
ramoncova06
Specialist III
Specialist III

can you share an example of the whole data set, since it seems as one row is not going to do the trick

vchuprina
Specialist
Specialist
Author

Numbers in table shifted by one row

Untitled.png

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
PabloOrtiz
Partner - Creator
Partner - Creator

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;

ramoncova06
Specialist III
Specialist III

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;

maxgro
MVP
MVP

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,

1.png