topic Re: Create new table in script in QlikView

Create new table in script

Anonymous — Tue, 26 Jun 2018 07:41:33 GMT

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

Re: Create new table in script

ogautier62 — Tue, 26 Jun 2018 09:24:09 GMT

Hi,

look at this :

https://community.qlikview.com/thread/293932

regards

Re: Create new table in script

MarcoWedel — Tue, 26 Jun 2018 23:47:49 GMT

Hi,

maybe one solution could be:

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