Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Any body please help me to extract PRD_CODE from this data loading.The PRD_CODE is in the policy_number field which are (1C,1D,1B,3F,1F,1X). Since the length of the policy numbers are different, right function, I applied did not work for all the numbers due to the length issue of the policy numbers.But there should be some way out to write this in the script just below the policy number.Hope some body will help me to resolve this.
Directory;
LOAD POLICY_NUMBER,
REF_NUMBER,
PREMIUM
FROM
[..\POL_NUMBER.xlsx]
(ooxml, embedded labels, table is Sheet1);
Thanks for the solution.It worked.
Hi Neville,
See the bellow code to see if this is what you need (i also attached a QVW but it may be possible that your using Sense).
sample:
Load * Inline
[
POLICY_NUMBER,REF_NUMBER,PREMIUM
01RT1C00071,145,1
01RT1C000699,115,1
01RT1C000711,116,2
02RT1D000862,120,3
04rT1B001008,121,4
99RT3F002514,132,5
RA00141F0001087,135,6
RA00141X0000973,125,7
];
codes:
Load * Inline
[
Code
1C
1D
1B
3F
1F
1X
];
for i = 0 to NoOfRows('sample')-1
let vPolicy = peek('POLICY_NUMBER',$(i),'sample');
for each vCode in FieldValueList('Code')
trace $(vCode);
if (index('$(vPolicy)','$(vCode)')>0) then
exit for;
end if;
next;
data:
Load
'$(vPolicy)' as Policy,
'$(vCode)' as Code
AutoGenerate(1);
next i;
drop table codes,sample;
Felipe.
Dear Felip,
Since there are large number of policy records,use of inline feature looks difficult to me.Would you mine explaining me a simple method I could use like right or left function that we use in Excel!
Thanks for your reply very fast
For the left and right type of solution, you would need to check different indexes for each text you want to search (1C,1D,1B,3F,1F,1X) since you don't have any type of delimiter or any other type of defined formula (something like, the Policy code starts at the 5th character of the string).
You could do that with a nested if, something like:
sample:
LOAD
if
(
index(POLICY_NUMBER,'1C')>0,
'1C',
if
(
index(POLICY_NUMBER,'1D')>0,
'1D',
... // And so on, for every text you want to search
) as POLICY_CODE,
REF_NUMBER,
PREMIUM
FROM
[..\POL_NUMBER.xlsx]
(ooxml, embedded labels, table is Sheet1);
For my solution, I just used the inlines for example purposes, you can add your own tables there, something like this
sample:
LOAD POLICY_NUMBER,
REF_NUMBER,
PREMIUM
FROM
[..\POL_NUMBER.xlsx]
(ooxml, embedded labels, table is Sheet1);
codes:
Load
Code
From [Excel file where the different codes you have are]
// This tests all the rows of the sample table, where the policy numbers are
for i = 0 to NoOfRows('sample')-1
// gets the policy number of the current row
let vPolicy = peek('POLICY_NUMBER',$(i),'sample');
// tests for each individual line of the codes table, to check wich code it is
for each vCode in FieldValueList('Code')
// verifies if there's an ocurrence of the currently code in the string from the data table, if so, exits for
if (index('$(vPolicy)','$(vCode)')>0) then
exit for;
end if;
next;
// loads the correspondent resulting table for each row
data:
Load
'$(vPolicy)' as Policy,
'$(vCode)' as Code
AutoGenerate(1);
next i;
drop table codes,sample;
Thanks for the solution.It worked.