Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

Extrcting PRD_code

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);

1 Solution

Accepted Solutions
nevilledhamsiri
Specialist
Specialist
Author

Thanks for the solution.It worked.

View solution in original post

4 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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.

nevilledhamsiri
Specialist
Specialist
Author

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

felipedl
Partner - Specialist III
Partner - Specialist III

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;

nevilledhamsiri
Specialist
Specialist
Author

Thanks for the solution.It worked.