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

How load data if it located one above the other?

vchuprina — Thu, 18 Jun 2015 16:12:28 GMT

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.

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

ramoncova06 — Thu, 18 Jun 2015 16:42:45 GMT

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?

Kushal_Chawda — Thu, 18 Jun 2015 16:47:01 GMT

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

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

vchuprina — Thu, 18 Jun 2015 16:58:55 GMT

You are right

Please see screen from file

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

vchuprina — Thu, 18 Jun 2015 16:59:30 GMT

Data without spaces between rows

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

ramoncova06 — Thu, 18 Jun 2015 17:04:36 GMT

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

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

vchuprina — Thu, 18 Jun 2015 17:21:20 GMT

Numbers in table shifted by one row

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

PabloOrtiz — Thu, 18 Jun 2015 17:57:28 GMT

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;

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

ramoncova06 — Thu, 18 Jun 2015 18:02:21 GMT

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;

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

maxgro — Thu, 18 Jun 2015 19:07:01 GMT

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,

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

MarcoWedel — Thu, 18 Jun 2015 23:08:25 GMT

Hi,

another solution could be:

tabTemp:
LOAD @1 FROM [https://community.qlik.com/thread/169008] (html, codepage is 1252, no labels, table is @3);

mapColNam:
Mapping
LOAD RecNo(), @1
Resident tabTemp
Where RecNo()<=3;

tabColVal:
Generic
LOAD Div(RecNo()-1,3) as ID,
    ApplyMap('mapColNam',Mod(RecNo()-1,3)+1),   
    @1   
Resident tabTemp
Where RecNo()>3;

DROP Table tabTemp;

hope this helps

regards

Marco