Can Qlik Sense load Data in unusual format?

[fzalexanderjohn]

Hello,

I'm loading Data from SAP with the SAP BAPI connector using the RSAQ_REMOTE_QUERY_CAL function.  This works well enough except that the resulting data is in a strange format. From https://blogs.sap.com/2014/09/11/downloading-a-sq01-or-sqvi-query-with-net-connector/ I found:

3. We need to parse the output data and store them to our table’s rows. The format of LDATA is

LEN:FIELD_DATA,LEN:FIELD_DATA,LEN:FIELD_DATA;LEN:FIELD_DATA,LEN:FIELD_DATA,LEN:FIELD_DATA;

where each field content is prefixed with a number determining it’s length in characters. ‘,’ is a field separator, ‘;’ is a row separator and ‘:’ sparates field length from the data itself. In a first step we concatenate all the LDATA lines into one single string (ldatastr). 

It looks like this:

000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:1600,005:99021,021:Postenverkauf Dresden,000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:1200,005:99021,021:Postenverkauf Dresden,000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:1300,005:99021,021:Postenverkauf Dresden,000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:1400,005:99021,021:Postenverkauf Dresden,000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:1500,005:99021,021:Postenverkauf Dresden,000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:1700,005:99021,021:Postenverkauf Dresden,000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:1900,005:99021,021:Postenverkauf Dresden,000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:2100,005:99021,021:Postenverkauf Dresden,000:,000:,000:,000:,005:01156,007:Dresden,000:,002:ZA,005:99021;004:1100,006:114802,031:Eduard Gringinger GmbH & Co. KG,000:,000:,000:,

How can I approach loading that in such strange formats? Is it even possible with Qlik Sense?

[rwunderlich]

Assume the data you posted is already loaded into a resident table named "Input" and the field is named "rawdata"

Parsed:
LOAD
  RecId,
  SubField(SubField(Row, ','), ':', 2) as Value
;
LOAD
  RecNo() as RecId,
  *
;
LOAD
  SubField(rawdata, ';') as Row
Resident Input
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[fzalexanderjohn]

Hello Rob,

thank you for your reply, it helped me a lot and I'm very close to having it the way I want. 

Currently the resulting table looks like this. 

RecNo	Value
1	ABC
1	DEF
2	XXX
2	YYY

How should I approach this to get a table that looks like so:

ABC	DEF
XXX	YYY

Would be great if you could point me in the right direction! 🙂

[rwunderlich]

Do you mean the odd numbered records contain the field names and the even number records contain the field values?

-Rob