topic Turn string value into individual rows in QlikView

Turn string value into individual rows

Rfoot — Tue, 13 Dec 2022 21:11:04 GMT

Hi, The above image shows the ID with string values.

How do I split the individual string values into its own subfield?

Re: Turn string value into individual rows

Rfoot — Tue, 13 Dec 2022 21:52:33 GMT

I have managed this;

PO_RECEIPTS_tmp:
NoConcatenate
LOAD
PO_RECEIPTS__ID,
PO_REC_PO_ORDER,
"PO Creditor",
"Rec Status",
"Rec Order",
substringcount("Rec Order",'ý') + 1 as Rec.order.count, //Number of subfields in Rec order
//PO_REC_STATUS_DISP as [PO Status],
PO_REC_COMPLETE,
PO_REC_DATE,
PO_REC_PERIOD,
PO_REC_YEAR,
PO_REC_TOTAL
FROM [lib://CDC_QVD_ALL/OZONE/Transaction Data/PO_RECEIPTS.qvd]
(qvd);

for i = 1 to Rec.order.count // should go from 1 to subfield count?
LOAD
SubField("Rec Order",'ý', $(i)) as Rec.order
Resident PO_RECEIPTS_tmp;

next i

The following error occurred:

The error occurred here:

for i = 1 to Rec.order.count

Re: Turn string value into individual rows

MarcoWedel — Tue, 13 Dec 2022 22:14:17 GMT

There is no need for fancy loops if you use the two parameter version of the subfield() function:

LOAD SubField([Rec Order],'ý') as RecOrder FROM [lib://CDC_QVD_ALL/OZONE/Transaction Data/PO_RECEIPTS.qvd] (qvd);

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/StringFunctions/SubField.htm#:~:text=If%20you%20use%20the%20Subfield()%20function%20in%20a%20LOAD%20statement%20with%20the%20optional%20field_no%20parameter%20left%20out%2C%20one%20full%20record%20will%20be%20generated%20for%20each%20substring.

Re: Turn string value into individual rows

Rfoot — Wed, 14 Dec 2022 01:19:12 GMT

Appreciate the help!