Add a unique id field for each xml file loaded for each row

[#1]

Good Morning to the QV's community,

I have a local folder in which there is xml files and they have the same structure. I used the wizard to get the script for the extraction of one xml file. Then I used the following loop :

set Root="C:\myFolder";
FOR each File in filelist (Root&'\*.xml')
'REPORT/HISTORY/data':
LOAD date,
          data%Table,
    %Key_HISTORY_578652BE74995C98    // Key to parent table: REPORT/HISTORY
FROM $(File) (XmlSimple, Table is [REPORT/HISTORY/data]);
REPORT:
LOAD TITRE,
    [EMN_Param/TBoxMS16/name] as name,
    [EMN_Param/TBoxMS16/Id] as Id,
    [EMN_Param/TBoxMS16/Status] as Status,
    [EMN_Param/TBoxMS16/RSSI] as RSSI,
    [EMN_Param/TBoxMS16/Caliber] as Caliber,
    %Key_REPORT_BE57120470E6A47F    // Key for this table: REPORT
FROM $(File) (XmlSimple, Table is [REPORT]);
NEXT File

You have to know that there is just 1 row in the table REPORT for each file.

What I want it is to get a table with date, data%Table and Id

date          date%Table          Id
01/01/2011    1000                23
02/01/2011    1200                23
01/01/2011    5000                12
02/01/2011    5500                12
and so on ... for each file 

I tried some script but I can't get what I want. Do you have an idea ?

Thank you very much !

Jérémy

[erichshiino]

Hi, I'm not sure if you want the ID on the Report/History table or only in the Report table.

I think this will be the idea anyway.

Include a field to identify the source file and to create the row number as an ID:


'REPORT/HISTORY/data':
LOAD date,$(File) as sourceFile,

rowno() as ID,

          data%Table,
    %Key_HISTORY_578652BE74995C98    // Key to parent table: REPORT/HISTORY
FROM $(File) (XmlSimple, Table is [REPORT/HISTORY/data]);

Maybe you will want to include this on the other table, the syntax will be the same.

This ID will not be repeated on this table. If you want to restart the ID for each file, you can use recno()

You can search about more differences between the functions on the QlikView help.

Hope this helps,

Erich

[#3]

Hi Erich, thank yyou for your answer.

My aim is a bit different, assume that I have the following tables in each file :

table1:

Date Data

table2:

ID

First file I've :

table1:

Date               Data

01/01/2011    123

...

table2:

ID

23

Second file I've :

table1:

Date               Data

01/01/2011    5000

...

table2:

ID

99

What I want it is :

table3:

Date                Data           Id

01/01/2011    123             23

...                              .          23

01/01/2011    5000           99

...                                         99

Thank you a lot !

[erichshiino · Accepted solution]

Try this then:

We can use the file name as a key between the tables to apply the ID on the report/history table

'REPORT/HISTORY/data':
LOAD date,
          data%Table,$(FILE) as Source,
    %Key_HISTORY_578652BE74995C98    // Key to parent table: REPORT/HISTORY
FROM $(File) (XmlSimple, Table is [REPORT/HISTORY/data]);
left join('REPORT/HISTORY/data')
//REPORT:
LOAD TITRE,$(FILE) as Source,
    [EMN_Param/TBoxMS16/name] as name,
    [EMN_Param/TBoxMS16/Id] as Id,
    [EMN_Param/TBoxMS16/Status] as Status,
    [EMN_Param/TBoxMS16/RSSI] as RSSI,
    [EMN_Param/TBoxMS16/Caliber] as Caliber,
    %Key_REPORT_BE57120470E6A47F    // Key for this table: REPORT
FROM $(File) (XmlSimple, Table is [REPORT]);

[#5]

Hi !

Th'at's work perfect !

Thank you

[qvhjenvo]

Hi,

I have  tried to study this examble. But I do not understand what "data%Table" is.

Someone can tell me what it and how to use it?

Regards

/Loi