Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vchuprina
New Contributor II

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: How load data if it located one above the other?

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

10 Replies
ramoncova06
Valued Contributor III

Re: How load data if it located one above the other?

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 ?

Re: How load data if it located one above the other?

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

vchuprina
New Contributor II

Re: How load data if it located one above the other?

You are right

Please see screen from file

Untitled.png

vchuprina
New Contributor II

Re: How load data if it located one above the other?

Data without spaces between rows

ramoncova06
Valued Contributor III

Re: How load data if it located one above the other?

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

vchuprina
New Contributor II

Re: How load data if it located one above the other?

Numbers in table shifted by one row

Untitled.png

pablo_ortiz
Contributor

Re: How load data if it located one above the other?

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
Valued Contributor III

Re: How load data if it located one above the other?

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;

MVP
MVP

Re: How load data if it located one above the other?

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

Community Browser