topic Re: Populating Field name from a different table in QlikView

Populating Field name from a different table

Anonymous — Mon, 11 Sep 2017 04:16:20 GMT

Hi All,

It would be great if someone could give me some direction with the following data load.

I get data in two excel worksheets. One sheet contains the field names and the other contains the value.

ex.

FIELD_NAME-Sheet1

----------------------------------------------

1 Emp_Name

2 Department

3 Field name 3

. .......................

n Field name n

-----------------------------------------------

VALUE_TABLE-Sheet 2

-------------------------------------------------------------------------------------------------------------

Peter Finance Filed3_ValP ................ Field_n_Pt

Susan HR Filed3_Val_Sus ................ Field_n_Su

Sam PR Filed3_Val_Sam ................ Field_n_Sa

-------------------------------------------------------------------------------------------------------------

I am trying to load this into Qlik

and get the below table

Emp_Name Department Field name 3 ................. Field name n

-----------------------------------------------------------------------------------------------------

Peter Finance Filed3_ValP ................ Field_n_Pt

Susan HR Filed3_Val_Sus ................ Field_n_Su

Sam PR Filed3_Val_Sam ................ Field_n_Sa

Any recommendations?

Thanks,

Aji Paul

Edit: (12/9/2017)

The number of the fields vary from file to file and is in excel 2016 xlsx format.

Re: Populating Field name from a different table

its_anandrjs — Mon, 11 Sep 2017 07:22:29 GMT

Only try to load the second table

EmpDetailTab:

Load

@1 as Emp_Name,

@2 as Department

From Source;

Re: Populating Field name from a different table

shraddha_g — Mon, 11 Sep 2017 07:24:13 GMT

might be something like this:

Load

Peter as Emp_Name,

Finance as Department

From Value_table;

Concatenate

Load *Inline [

Emp_Name , Department

Peter, Finance

];

Re: Populating Field name from a different table

Anonymous — Mon, 11 Sep 2017 08:12:42 GMT

Try below one

LOAD [Emp_Name ] as Emp_Name,

[Department ] as Department

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Rotate(right),

Transpose(),

Rotate(right),

Remove(Row, Pos(Top, 1)),

Remove(Col, Pos(Top, 1))

));

Concatenate

LOAD @1 as Emp_Name,

@2 as Department

FROM

(ooxml, no labels, table is Sheet2, filters(

Remove(Row, Pos(Top, 1))

));

Re: Populating Field name from a different table

Kushal_Chawda — Mon, 11 Sep 2017 14:02:59 GMT

If you want it dynamic then use below

Data:

LOAD @1,

FROM

[test.xls]

(biff, no labels, table is Sheet2$);

FieldName:

LOAD @1 as Sort,

@2 as FieldName

FROM

[test.xls]

(biff, no labels, table is Sheet1$);

For i=1 to FieldValueCount('Sort')

let vFieldName=FieldValue('FieldName',$(i));

let vField = '@' & '$(i)';

RENAME Field $(vField) to $(vFieldName);

DROP Table FieldName;

OR you can simple rename the Field names like below

Data:

LOAD @1 as Emp_Name,

@2 as Dept_Name

FROM

[test.xls]

(biff, no labels, table is Sheet2$);

Re: Populating Field name from a different table

Anonymous — Tue, 12 Sep 2017 01:09:50 GMT

Thanks Kushal.

This is very close to what I am looking for.

My file is in xlsx format. So the field names would be A, B, ... AA etc (not @1,@2).

Another caveat is, the number of fields will vary by files. I am trying to create an automated load.

Any suggestions?

Thanks,

Aji Paul

Re: Populating Field name from a different table

Kushal_Chawda — Tue, 12 Sep 2017 07:38:51 GMT

Data:
LOAD *
FROM
[test_1.xlsx]
(ooxml, no labels, table is Sheet1);


T1:
NoConcatenate
First 1 LOAD *
FROM
[test_1.xlsx]
(ooxml, no labels, table is Sheet1);


Fields:
CrossTable(Field,FieldName)
LOAD RowNo() as Rec,
     *
Resident T1;


DROP Table T1;


FieldNames:
NoConcatenate
LOAD RowNo() as Row,
     *
Resident Fields;


DROP Table Fields;


DROP Fields Rec,FieldName;


Left Join(FieldNames)
LOAD A as Row, 
     B as FieldNames
FROM
[test_1.xlsx]
(ooxml, no labels, table is Sheet2);


For i=1 to FieldValueCount('FieldNames')


let vFieldName=FieldValue('FieldNames',$(i));


let vField = FieldValue('Field',$(i));


RENAME Field $(vField) to $(vFieldName);


NEXT


DROP Table FieldNames;

Re: Populating Field name from a different table

qliksus — Wed, 13 Sep 2017 06:10:16 GMT

hi ,

When you do enable transformation step even in XLSX you can generate fields as @1,@2 ....... etc so do some dummy enable transformation like the below

Data:

Dummy Transformation:

Select None in Lables after the transformation

Now once you have the data in the below format

LOAD @1,

@2,

@3,

@4,

@5,

@6,

@7,

@8,

@9,

@10,

@11,

@12,

@13

FROM

[Distinguish.xlsx]

(ooxml, no labels, table is Sheet3, filters(

Replace(1, top, StrCnd(longer, 0))

));

Load the Fieldmapping excel as a mapping table and use the rename function to change the field name

Fieldmap:
mapping LOAD '@'&SNO as SNO,
FieldName
FROM

(ooxml, embedded labels, table is Sheet2);

Rename fields using Fieldmap;

you will see the field renamed as below

Re: Populating Field name from a different table

Anonymous — Wed, 27 Sep 2017 02:03:55 GMT

Brilliant! Thanks Kushal. This is exactly I was looking for.

The only thing I changes was adding [ ] for renaming the field as I have spaces in some fields.