Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

11 Replies
Siva_Sankar
Master II
Master II

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
Master
Master

Hi Gene,

Can you attached your 3 xml files?

Regards,

Sokkorn

Anonymous
Not applicable
Author

Hope this helps.

Sokkorn
Master
Master

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

Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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?

Not applicable
Author

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
Master
Master

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