Populating Field name from a different table

[Anonymous]

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.

[its_anandrjs]

Only try to load the second table

EmpDetailTab:

Load

@1 as Emp_Name,

@2 as Department

From Source;

[shraddha_g]

might be something like this:

Load

Peter as Emp_Name,

Finance as Department

From Value_table;

Concatenate

Load *Inline [

Emp_Name , Department

Peter, Finance

];

[Anonymous]

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

));

[Kushal_Chawda]

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

[Anonymous]

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

[Kushal_Chawda · Accepted solution]

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]

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

[Anonymous]

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.