topic Re: Can Qlik Sense load Data in unusual format? in Connectivity & Data Prep

Can Qlik Sense load Data in unusual format?

fzalexanderjohn — Tue, 28 Dec 2021 20:26:42 GMT

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?

Re: Can Qlik Sense load Data in unusual format?

rwunderlich — Fri, 15 May 2020 16:21:03 GMT

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

Re: Can Qlik Sense load Data in unusual format?

fzalexanderjohn — Sun, 17 May 2020 07:59:30 GMT

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! 🙂

Re: Can Qlik Sense load Data in unusual format?

rwunderlich — Sun, 17 May 2020 23:47:12 GMT

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

-Rob