Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Create new table in script

Hi,

I've got two tables. One with a list of field name and one with the corresponding data (see attechments).

tblFieldnames:

FieldNames        FNSequence

GMT               1

Date              2

Item              3

Name              4

User              5

Comment           6

tblData:

Dataset       Data                 DTSequence

1             1526277833           1

1             14/May/2018 08:03:53 2

1             Item1                3

1             Name1                4

1             User1                5

1             Comment1             6

2             1526277833           7

2             14/May/2018 08:03:53 8

2             Item2                9

2             Name2                10

2             User2                11

2                                  12

3             1526277833           13

3             14/May/2018 08:03:53 14

3             Item3                15

3             Name3                16

3             User3                17

3             Comment3             18

4             1526277848           19

4             14/May/2018 08:04:08 20

4             Item4                21

4             Name4                22

4             User4                23

4                                  24


How do I combine this two tables to get one new with the fields filled with the data?


NewTable:

DataSet    GMT           Date                    Item     Name     User     Comment

1          1526277833    14/May/2018 08:03:53    Item1    Name1    User1    Comment1

2          1526277833    14/May/2018 08:03:53    Item2    Name2    User2

3          1526277833    14/May/2018 08:03:53    Item3    Name3    User3    Comment3

4          1526277848    14/May/2018 08:04:08    Item4    Name4    User4   

Thanks a lot for your help!

Michael

1 Solution

Accepted Solutions
Highlighted

Hi,

maybe one solution could be:

QlikCommunity_Thread_305989_Pic1.JPG

mapFields:

Mapping

LOAD FNSequence,

    FieldNames

FROM [https://community.qlik.com/servlet/JiveServlet/download/1507274-329843/tblFieldnames.xlsx] (ooxml, embedded labels, table is Tabelle1);

tabTemp:

Generic

LOAD Dataset,

    AutoNumber(DTSequence,Dataset),

    Data

FROM [https://community.qlik.com/servlet/JiveServlet/download/1507274-329844/tblData.xlsx] (ooxml, embedded labels, table is Tabelle1);

table1:

LOAD '' as TempField AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'tabTemp.*') THEN

    JOIN (table1) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

DROP Field TempField;

RENAME Fields using mapFields;

hope this helps

regards

Marco

View solution in original post

2 Replies
Highlighted
Specialist II
Specialist II

Highlighted

Hi,

maybe one solution could be:

QlikCommunity_Thread_305989_Pic1.JPG

mapFields:

Mapping

LOAD FNSequence,

    FieldNames

FROM [https://community.qlik.com/servlet/JiveServlet/download/1507274-329843/tblFieldnames.xlsx] (ooxml, embedded labels, table is Tabelle1);

tabTemp:

Generic

LOAD Dataset,

    AutoNumber(DTSequence,Dataset),

    Data

FROM [https://community.qlik.com/servlet/JiveServlet/download/1507274-329844/tblData.xlsx] (ooxml, embedded labels, table is Tabelle1);

table1:

LOAD '' as TempField AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'tabTemp.*') THEN

    JOIN (table1) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

DROP Field TempField;

RENAME Fields using mapFields;

hope this helps

regards

Marco

View solution in original post