topic Add a unique id field for each xml file loaded for each row in QlikView

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

Wed, 22 Jun 2011 07:58:10 GMT

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

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

erichshiino — Wed, 22 Jun 2011 11:50:14 GMT

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

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

Wed, 22 Jun 2011 12:07:48 GMT

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:

First file I've :

table1:

Date Data

01/01/2011 123

...

table2:

Second file I've :

table1:

Date Data

01/01/2011 5000

...

table2:

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 !

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

erichshiino — Wed, 22 Jun 2011 12:40:13 GMT

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

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

Thu, 30 Jun 2011 07:58:02 GMT

Hi !

Th'at's work perfect !

Thank you

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

qvhjenvo — Mon, 28 Aug 2017 08:52:28 GMT

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