topic Re: Excel formulation in QlikView in QlikView

Excel formulation in QlikView

Thu, 07 Jan 2016 09:38:28 GMT

Hello,

Does someone could inform me about the feasibility to retrieve some characters ("as STXT (N3; 6; 2)" in excel) but with QlikView? and create a new field?

Then, once this character has been recovered, is it possible to assign a specific name?

My example in Excel:

=SI(STXT(L2;6;1)="0";"DLP1";SI(STXT(L2;6;1)="1";"DLP1";SI(STXT(L2;6;1)="2";"DLP2";SI(STXT(L2;6;1)="3";"DLP2";"Unknown"))))

Thank you in advance for your assistance,

Best regards

Matthieu

Re: Excel formulation in QlikView

MK_QSL — Thu, 07 Jan 2016 09:48:03 GMT

I think..

STXT you can use MID function in QlikView

Re: Excel formulation in QlikView

sunny_talwar — Thu, 07 Jan 2016 10:00:44 GMT

Would you be able to share some sample data with expected output?

Re: Excel formulation in QlikView

Thu, 07 Jan 2016 10:07:44 GMT

Hello,

Thanks for your answer, it is very useful.

Do you have a proposition regarding 2nd question ?

I mean, once I have recovered the appropriate characters,

Ex:

If I retrieve 10R or 20L, could you tell me if I can assign a name for each number or letter ?

1 = machine A

0= machine B

R=machine C

...

Let me know if I am not clear ?

regards,

Re: Excel formulation in QlikView

tamilarasu — Thu, 07 Jan 2016 10:11:15 GMT

Hi Matthieu,

Try,

Pick(Match(Mid(FieldName,1,6), 0, 1, 2, 3)+1, 'DLP1', 'DLP1', 'DLP2', 'DLP2', 'Unknown')

Change the FieldName as per your application.

Re: Excel formulation in QlikView

Anonymous — Thu, 07 Jan 2016 10:15:40 GMT

Most of the formulation you can achieve of Excel in qlikview..How ever name and syntax would be different.

As Manish Kachhia

suggested here assume you are having a field in QV named like Stext with value of 123er4 then, you can use mid() like this:

mid(FieldName,StartNum,EndNum) as NewFieldValue;

Re: Excel formulation in QlikView

sasikanth — Thu, 07 Jan 2016 10:15:47 GMT

hi,

Try this expression

IF( mid(TextField,6,1)=0 OR mid(Text,6,1)=1,'DLP1',

IF(mid(Text,6,1)=2, OR mid(text,6,1)=3,'DLP2'))

Re: Excel formulation in QlikView

Anonymous — Thu, 07 Jan 2016 12:36:33 GMT

If you have data like 1,0,R in a Field say NewField, then you can do like this at script:

If(NewField='1', 'Machine A',

If(NewField='0', 'Machine B',

If(NewField='R', 'Machine C'))) as FieldName

Re: Excel formulation in QlikView

Thu, 07 Jan 2016 12:40:10 GMT

you can use pick and match function

Re: Excel formulation in QlikView

Mon, 11 Jan 2016 08:02:11 GMT

Hello Sunny K,

I have tried your expression and it works very well !

Thank you very much.

Regards,

Matt

Re: Excel formulation in QlikView

Mon, 11 Jan 2016 08:10:39 GMT

Hello all,

Thank you for your assistance. I found the solution with the mid function and thanks to you.

However, I have tried with pick match function and I do not understand well why it is not working ?

Regards,

Re: Excel formulation in QlikView

sasikanth — Mon, 11 Jan 2016 12:43:49 GMT

Welcome