Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qvhelpplease
New Contributor II

loading data

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 ?

gwassenaarswuehlkush141087maxgrorwunderlichmrkachhiaimpstalwar1sorrakis01

1 Solution

Accepted Solutions
Employee
Employee

Re: loading data

PFA a possible solution

8 Replies
Employee
Employee

Re: loading data

Would you mind sharing sample of your files?

qvhelpplease
New Contributor II

Re: loading data

please find the attached files of header and transaction data

MVP
MVP

Re: loading data

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;

qvhelpplease
New Contributor II

Re: loading data

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

Employee
Employee

Re: loading data

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);

Employee
Employee

Re: loading data

PFA a possible solution

qvhelpplease
New Contributor II

Re: loading data

u made my life easy   thanks

i will keep you asking more questions in future

Re: loading data

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;

Community Browser