Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
ajipaul02
New Contributor II

Populating Field name from a different table

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.

Tags (1)
1 Solution

Accepted Solutions

Re: Populating Field name from a different table

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;

8 Replies

Re: Populating Field name from a different table

Only try to load the second table

EmpDetailTab:

Load

@1 as Emp_Name,

@2 as Department

From Source;

Partner
Partner

Re: Populating Field name from a different table

might be something like this:

Load

Peter as Emp_Name,

Finance as Department

From Value_table;

Concatenate

Load *Inline [

Emp_Name , Department

Peter, Finance

];

lakkydev
Contributor II

Re: Populating Field name from a different table

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

If  you want it dynamic then use below

Data:

LOAD @1,

    @2

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

NEXT

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

ajipaul02
New Contributor II

Re: Populating Field name from a different table

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

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;

qliksus
Valued Contributor II

Re: Populating Field name from a different table

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

ajipaul02
New Contributor II

Re: Populating Field name from a different table

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.