Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]);
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
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 !
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]);
Hi !
Th'at's work perfect !
Thank you
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