Can't use Wildcards on field names, what can I do?

[Anonymous]

Hello All, I'm loading data from ~100 xml files which have the same field definitions except for the key.  The key is suffixed with random numbers e.g. key12345. 

Based on the manual I cannot use wildcards in fields names so I cannot say

Load

     date,

     name,

     key* as key

from mydir\*.xml.

I thought about Load * and then using FieldName() function to retrieve the field name, but load * doesn't work with xml files.

What can I do?

I do not want to create 100 load statements.

Thanks,

Gene

[Siva_Sankar]

I have no idea, but you can refer here http://community.qlik.com/thread/71373 and

http://community.qlik.com/message/437644#437644

May be helpful.

Regards

Siva

[Sokkorn]

Hi Gene,

Can you attached your 3 xml files?

Regards,

Sokkorn

[Anonymous]

Hope this helps.

[Sokkorn]

Hi Gene,

You may try this

For Each vKeyField in 'key_123','key_234','key_456','key_789'
  [tmp]:
  LOAD
  $(vKeyField) AS _Key,
  date,
    name
  FROM  (XmlSimple, Table is [item]);
NEXT vKeyField


[Data]:
NoConcatenate
LOAD * Resident [tmp] Where Len([_Key])>0;
DROP Table [tmp];

Regards,

Sokkorn

[#6]

Hi Gene,

Based on the files you attached, you could try the below script (just change the path in the first statement):

for each vFile in FileList('C:\Users\Test\Desktop\QVW\*.xml')

Keys:
LOAD @1,
     @2,
     @3,
     Mid(@4, 1, Index(@4, '=')-1) as MyKeyField,
     '$(vFile)' as MyKeyFile
FROM
$(vFile)
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

next

for i = 0 to NoOfRows('Keys')-1

let vFile = Peek('MyKeyFile', $(i), 'Keys');
let vKey = Peek('MyKeyField', $(i), 'Keys');

item:
LOAD date,
     name,
     [$(vKey)] as KeyField
FROM $(vFile) (XmlSimple, Table is [item]);

next

DROP Table Keys;


This can save you from hard coding your keys (so it's worth a shot).

Otherwise if applying this on your actual production files doesn't work, then Sokkorn's suggestion is the most appropriate.

Hope this helps.

[Anonymous]

Thanks Sokkorn,

A couple of concerns with your solution.

1) I have to know all my field names prior to load.  I would prefer not to go through 100 xml files and put them into a list.  So the solution does not scale well.

2) x= number of files, y = number of distinct keys.  That load statement would load x*y files.  My xml files aren't as small as the ones I attached, so the solution will take a long time to refresh.

I haven't tested your solution, but I'm sure it would work.  Unfortunately I will not be implementing due to above concerns.

Do you have any other ideas?

[Anonymous]

Hi Jean-Pierre,

My xml is not as simple as I provided; it is multiline with many more attributes and values.  That was just as an example.  I will not be able to treat it as text delimitered, and have to read it as XML.

Since I have to read my data as XML I cannot use @fieldnumber approach, or am I mistaken here?

[#9]

Yeah, the @fieldnumber doesn't seem to work, it could be a useful feature though.

If you can upload your real structure with sample data this would be helpful.

The script may need little tweaking (am i too optimistic ).

[Sokkorn]

Hi Gene,

Agreed with your concern. Let try this one

[tmpField]:
First 1 LOAD
  [@1:n] AS [FullField]
FROM  (fix, codepage is 1252);

[XMLField]:
NoConcatenate
LOAD Concat(DISTINCT Chr(39) & [FieldName] & Chr(39),',') AS [FieldName];
LOAD
  Trim(SubField([FullField],'"',3)) AS [FieldName] Where WildMatch([FullField],'*key_*') and NOT WildMatch([FullField],'*/>*');
LOAD
  SubField([FullField],'=') AS [FullField]
Resident [tmpField];

Let vAllKeyField = Peek('FieldName',0,'XMLField');

DROP Tables [tmpField],[XMLField];

For Each vEachKeyField in $(vAllKeyField)
  [tmp]:
  LOAD
       $(vEachKeyField) AS _Key,
       date,
         name
  FROM  (XmlSimple, Table is [item]);
NEXT vEachKeyField

[Data]:
NoConcatenate
LOAD * Resident [tmp] Where Len([_Key])>0;
DROP Table [tmp];

Let vAllKeyField = Null();
Let vEachKeyField = Null();

This quite hard script, but it can help us to get start.

Regards,

Sokkorn