topic Re: loading data in QlikView

loading data

Anonymous — Fri, 17 Jun 2016 17:23:08 GMT

Hi All,

I have a excel with headers of data , text file with all the data without headers ,how can i load this data into qlikview .any ideas ?

gwassenaar‌swuehl‌kush141087‌maxgro‌rwunderlich‌mrkachhiaimp‌stalwar1‌sorrakis01‌

Re: loading data

Clever_Anjos — Fri, 17 Jun 2016 17:25:34 GMT

Would you mind sharing sample of your files?

Re: loading data

Anonymous — Fri, 17 Jun 2016 17:28:55 GMT

please find the attached files of header and transaction data

Re: loading data

swuehl — Fri, 17 Jun 2016 17:32:26 GMT

Load your text file with option 'none' for labels, this will create a load like

Table:

LOAD @1,

@2,

@3,

@4,

@5,

@6,

@7,

@8,

@9,

@10,

@11

FROM

(txt, utf8, no labels, delimiter is ',', msq);

Do the similar for the header file, using an additional RecNo() as ID and a CROSSTABLE LOAD prefix, something like

CROSS:
CROSSTABLE (Header, FieldName)

LOAD Recno() as ID,

@1,

@2,

@3,

@4,

@5,

@6,

@7,

@8,

@9,

@10,

@11

FROM

(txt, utf8, no labels, delimiter is ',', msq)

;

Then create a MAP:

MAP:

MAPPING

LOAD Header, FieldName

RESIDENT CROSS;

And then rename your original fields:

RENAME FIELDS USING MAP;

Re: loading data

Anonymous — Fri, 17 Jun 2016 17:39:51 GMT

this might be very dumb ,but can i ask you for an QVW with the sample data i attached

Re: loading data

Clever_Anjos — Fri, 17 Jun 2016 17:40:37 GMT

Please check if fits your needs:

header:
LOAD *
FROM
[Header.xlsx]
(ooxml, embedded labels, table is Sheet1);

set str='';
For i = 1 to NoOfFields('header')
  let str = str & ',@' & i & ' as ' & FieldName(i,'header');
next
let str=mid(str,2);

LOAD $(str)
FROM
[transdata.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Re: loading data

Clever_Anjos — Fri, 17 Jun 2016 17:42:04 GMT

PFA a possible solution

Re: loading data

Anonymous — Fri, 17 Jun 2016 17:50:52 GMT

u made my life easy thanks

i will keep you asking more questions in future

Re: loading data

Kushal_Chawda — Sat, 18 Jun 2016 05:36:38 GMT

try this as well

Map_Field:
Mapping LOAD '@'&RecNo() as FieldFrom,
@1 as FieldTo
FROM

(ooxml, no labels, table is Sheet1, filters(
Transpose()
));

Data:
LOAD @1,
@2,
@3,
@4,
@5,
@6,
@7,
@8,
@9,
@10,
@11,
@12,
@13,
@14,
@15,
@16,
@17,
@18,
@19,
@20,
@21,
@22,
@23,
@24,
@25,
@26,
@27,
@28,
@29,
@30,
@31,
@32
FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

RENAME Fields using Map_Field;