Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nevilledhamsiri
Valued Contributor

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
Valued Contributor

Re: Extrcting PRD_code

Thanks for the solution.It worked.

4 Replies
felipedl
Valued Contributor III

Re: Extrcting PRD_code

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
Valued Contributor

Re: Extrcting PRD_code

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
Valued Contributor III

Re: Extrcting PRD_code

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
Valued Contributor

Re: Extrcting PRD_code

Thanks for the solution.It worked.