Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with a long text string value, 'BOMCost_Value', I am searching the text string for particular text, to extract text after it, and the number of times it occurs. I want to be able to run a for..next loop to pull out all occurrences. I have done this with the code below but it not very elegant when I have up to 27 occurrences.
BOMCost1:
LOAD
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED')+16,60) as cost_long1,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',2)+16,60) as cost_long2,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',3)+16,60) as cost_long3,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',4)+16,60) as cost_long4,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',5)+16,60) as cost_long5,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',6)+16,60) as cost_long6
Resident BOMCost;
I tried the following but 'mycounter' isn't available within the load.
Note : 'cost_count1' is the number of occurrences of the text within the string, which I have within the original BOMCost table.
for mycounter=1 to $(cost_count1);
set mycostid='cost_long' & mycounter;
BOMCost1:
Load
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',mycounter)+16,60) as mycostid
Resident BOMCost;
next;
Just related to your initial question I would probably something like this:
load
recno() as RecNo, rowno() as RowNo, iterno() as IterNo,
BOMCost_Value,
subfield(BOMCost_Value, 'VALUE_ENTERED', iterno()) as FieldPart
resident BOMCost while iterno() <= substringcount(BOMCost_Value, 'VALUE_ENTERED') +1;
whereby the split-loop comes from the bold part.
If I look on your data-example I would rather try to transform the whole string into a normal data-structur. This means using the above mentioned approach multiple times on top of each other, like:
load *, subfield(F3, ',', iterno()) as F4, iterno() as IterNo3
while iterno() <= substringcount(F3, ',') + 1;
load *, subfield(F2, ':', iterno()) as F3, iterno() as IterNo2
while iterno() <= substringcount(F2, ':') + 1;
load F, subfield(F, ';', iterno()) as F2, iterno() as IterNo1, recno() as RecNo
from Source while iterno() <= substringcount(F, ';') + 1;
If your field is reasonably created you won't not have too much difficulties to specify the appropriate delimiters to divide the multiple parts. Even if not there are often enough information included to find and/or replace the delimiter maybe by counting the number and positions of the different delimiters and the lengths of the parts and also using additionally more string-functions like textbetween(), mapsubstring() and so on. By including the shown recno/rowno/iterno it's very easy to comprehend which information comes from where and which adjustments return in which results. Just play a bit with it.
Much easier that this would be if you could connect to the source-database and not using such output ...
- Marcus
set NoOfQvds=3;
let vNum=1
do while vNum < $NoOfQvds)
let vStart=Peek('QVD_Name',S(vNum),'Temp1');
load * from [$(vStart)](cvd);
let vNum=$(vNum)+1
loop
It sounds like that there should be an easier way to that, may be using in combination with another string function. Could you please give a sample string and expected output in that context? - Let us try.
{"CUSTOM_COST_ITEM_1927246_1620717661322":{"COMMENT":null,"COMPONENT_ART_NR":"","COMPONENT_DESCRIPTION":"Power turbine rotor assembly","COMPONENT_NAME":"AEC3800","ATTRIBUTES":{"CPC column":{"NAME":"CPC column","VALUE":"cost_cpc_column_offshore_gt_core_engine","COST_ID":null},"package_location":{"NAME":"package_location","VALUE":"","COST_ID":null},"CPC row":{"NAME":"CPC row","VALUE":"cost_cpc_row_11AC1100","COST_ID":null},"Ref 2":{"NAME":"Ref 2","VALUE":"fsp_sgt_ref2_4023","COST_ID":null},"EIR Resolution":{"NAME":"EIR Resolution","VALUE":"GT Core Engine","COST_ID":null},"EIR Type":{"NAME":"EIR Type","VALUE":"","COST_ID":null}},"ID":"1927246","ADDER":"100.0","COST_PER_UNIT":{"CURRENCY":"GBP","DESCRIPTION":"V69330806-Core Equipment Labour","LOCATION":"lcn","LONG_DESCRIPTION":"Core Labour","MODIFY_DATE":"1469022563000","NAME":"cost_V69330806","REBATES":{"ENABLED":true,"SITE_NAME":"P01 - PG SC","REBATES":[{"ID":"0","ITEM_NR":"0","REBATE":"0.0"},{},{},{},{},{},{},{},{},{}]},"UNIT":"ea","VALUE":"1.0","VERSION":"1"},"REBATES":{"ENABLED":true,"SITE_NAME":"P01 - PG SC","REBATES":[{},{},{},{},{},{},{},{},{},{}]},"CURRENCY":"GBP","DB_ID":"0","DESCRIPTION":"V69330806-Core Equipment Labour","KEY":"CUSTOM_COST_ITEM_1927246_1620717661322","PRODUCT_DESCRIPTION":null,"QTY":"1","SUM":"27658.03","SUM_WITH_ADDER":"27658.03","VALUE_ENTERED":"27658.03","VERSION":"1","AG":"lcn_oscarcode_AEC38","CG":"","PG":"","Quality":"","SCG":"fsp_sgt_scg_a","TCG":"","WBS":""},"CUSTOM_COST_ITEM_1927247_1620717661322":{"COMMENT":null,"COMPONENT_ART_NR":"","COMPONENT_DESCRIPTION":"Power turbine rotor assembly","COMPONENT_NAME":"AEC3800","ATTRIBUTES":{"CPC column":{"NAME":"CPC column","VALUE":"cost_cpc_column_offshore_gt_core_engine","COST_ID":null},"package_location":{"NAME":"package_location","VALUE":"","COST_ID":null},"CPC row":{"NAME":"CPC row","VALUE":"cost_cpc_row_11AA1200","COST_ID":null},"Ref 2":{"NAME":"Ref 2","VALUE":"fsp_sgt_ref2_4023","COST_ID":null},"EIR Resolution":{"NAME":"EIR Resolution","VALUE":"GT Core Engine","COST_ID":null},"EIR Type":{"NAME":"EIR Type","VALUE":"","COST_ID":null}},"ID":"1927247","ADDER":"100.0","COST_PER_UNIT":{"CURRENCY":"GBP","DESCRIPTION":"V69330906-Core Equipment","LOCATION":"lcn","LONG_DESCRIPTION":"Core Equipment","MODIFY_DATE":"1402040852000","NAME":"cost_V69330906","REBATES":{"ENABLED":true,"SITE_NAME":"P01 - PG SC","REBATES":[{"ID":"0","ITEM_NR":"0","REBATE":"0.0"},{},{},{},{},{},{},{},{},{}]},"UNIT":"ea","VALUE":"1.0","VERSION":"1"},"REBATES":{"ENABLED":true,"SITE_NAME":"P01 - PG SC","REBATES":[{},{},{},{},{},{},{},{},{},{}]},"CURRENCY":"GBP","DB_ID":"0","DESCRIPTION":"V69330906-Core Equipment","KEY":"CUSTOM_COST_ITEM_1927247_1620717661322","PRODUCT_DESCRIPTION":null,"QTY":"1","SUM":"65632.98","SUM_WITH_ADDER":"65632.98","VALUE_ENTERED":"65632.98","VERSION":"1","AG":"lcn_oscarcode_AEC38","CG":"","PG":"","Quality":"","SCG":"fsp_sgt_scg_m","TCG":"","WBS":""}}
I use the following formula to find the number of occurances :
SubStringCount([BOMCost_Value],'AEC3800')
This gives me a value of 2. So i have two sets of cost data in the string.
Therefore the two output values are :
1. 27658.03","VERSION":"1","AG":"lcn_oscarcode_AEC38","CG":"","
2. 65632.98","VERSION":"1","AG":"lcn_oscarcode_AEC38","CG":"","
Two instances of substrings - that I got, but then how are you deciding the other parts of the strings in the final output? May be with a simpler example, like:
'AAXXX bbbb MMMM KKKKK TTTTKLMP sggsgsg 123;1233333; TTTTTpoltttaa&@' - is my string and we have two 'TTTT's in it, then we have to find two substrings from the entire long string. What is the logic for defining these two substrings? do you have a delimiter or separator?
Yes once I have the two substrings I then find the first comma to give me the actual number or text I want.
In this case the two strings I finally get are:
27658.03
&
65632.98
How are you getting the first substring - 27658.03","VERSION":"1","AG":"lcn_oscarcode_AEC38","CG":""," ?
I use formula below, where BOMCost_Value is the text string I gave above.
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',i)+16,60)
I have moved this thread to the QlikView App Development board since this is a scripting Question.
Just related to your initial question I would probably something like this:
load
recno() as RecNo, rowno() as RowNo, iterno() as IterNo,
BOMCost_Value,
subfield(BOMCost_Value, 'VALUE_ENTERED', iterno()) as FieldPart
resident BOMCost while iterno() <= substringcount(BOMCost_Value, 'VALUE_ENTERED') +1;
whereby the split-loop comes from the bold part.
If I look on your data-example I would rather try to transform the whole string into a normal data-structur. This means using the above mentioned approach multiple times on top of each other, like:
load *, subfield(F3, ',', iterno()) as F4, iterno() as IterNo3
while iterno() <= substringcount(F3, ',') + 1;
load *, subfield(F2, ':', iterno()) as F3, iterno() as IterNo2
while iterno() <= substringcount(F2, ':') + 1;
load F, subfield(F, ';', iterno()) as F2, iterno() as IterNo1, recno() as RecNo
from Source while iterno() <= substringcount(F, ';') + 1;
If your field is reasonably created you won't not have too much difficulties to specify the appropriate delimiters to divide the multiple parts. Even if not there are often enough information included to find and/or replace the delimiter maybe by counting the number and positions of the different delimiters and the lengths of the parts and also using additionally more string-functions like textbetween(), mapsubstring() and so on. By including the shown recno/rowno/iterno it's very easy to comprehend which information comes from where and which adjustments return in which results. Just play a bit with it.
Much easier that this would be if you could connect to the source-database and not using such output ...
- Marcus
set NoOfQvds=3;
let vNum=1
do while vNum < $NoOfQvds)
let vStart=Peek('QVD_Name',S(vNum),'Temp1');
load * from [$(vStart)](cvd);
let vNum=$(vNum)+1
loop