Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rajeshqvd
Contributor

Sub String

Hi All Please need help on below requirement in Qlik Sense

DataSale
(HH) ajshjklagdhh
(HP)kjhkjhghjghjghp2
(HH)gfghfgd
(HP)fdghdghdhgdg4
(HH)gjgjhjhjhhj
(HP)fdgdgggfggfg6
(HP)adsgfdghdgdg7

Out put

DataSaleForOUT PUT
(HH) ajshjklagdhh(HH)2
(HP)kjhkjhghjghjghp2(HP)0
(HH)gfghfgd(HH)4
(HP)fdghdghdhgdg4(HP)0
(HH)gjgjhjhjhhj(HH)13
(HP)fdgdgggfggfg6(HP)0
(HP)adsgfdghdgdg7(HP)0
Tags (1)
1 Solution

Accepted Solutions
rajeshqvd
Contributor

Re: Sub String

Data:

LOAD

    Data,

    Sale,

   Left(Data,4) as DES

   

FROM [lib://Shift/shiftedrows.xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

Load *, AutoNumber(DES) as NUM Resident Data;

Drop Table Data;

After i did this i am getting below output

     

DataHH & HP&HCNUMSaleTaxable
(HH) ajshjklagdhh(HH)1-44
(HP)kjhkjhghjghjghp(HP)22-
(HH)gfghfgd(HH)1-44
(HP)fdghdghdhgdg(HP)24-
(HH)gjgjhjhjhhj(HH)1-44
(HP)fdgdgggfggfg(HP)26-
(HP)adsgfdghdgdg(HP)27-
sadffffggf-35-
(HC)fgfgfgfgfgf(HC)49-
(HC)sdsdfsfs(HC)416-
fdtdfghdhfg-534-
sadasd-63-
sadasd-634-
asdasd-743-
adasd-843-
9 Replies
rajeshqvd
Contributor

Re: Sub String

Data:

LOAD

    Data,

    Sale,

   Left(Data,4) as DES

   

FROM [lib://Shift/shiftedrows.xlsx]

(ooxml, embedded labels, table is Sheet1);

Final:

Load *, AutoNumber(DES) as NUM Resident Data;

Drop Table Data;

After i did this i am getting below output

     

DataHH & HP&HCNUMSaleTaxable
(HH) ajshjklagdhh(HH)1-44
(HP)kjhkjhghjghjghp(HP)22-
(HH)gfghfgd(HH)1-44
(HP)fdghdghdhgdg(HP)24-
(HH)gjgjhjhjhhj(HH)1-44
(HP)fdgdgggfggfg(HP)26-
(HP)adsgfdghdgdg(HP)27-
sadffffggf-35-
(HC)fgfgfgfgfgf(HC)49-
(HC)sdsdfsfs(HC)416-
fdtdfghdhfg-534-
sadasd-63-
sadasd-634-
asdasd-743-
adasd-843-
ashwin_r
New Contributor II

Re: Sub String

Try This,

Load

Data,

'(' & TextBetween(Data,'(',')') & ')'  as For,

Sale.

*

from.........

Regards,

Ashwin.R

DavidŠtorek
Contributor III

Re: Sub String

Hi,

maybe we will be able to help you if you describe what you are trying to do. What shoul be the intended resul of this? What is input data?

rajeshqvd
Contributor

Re: Sub String

out put should be like this

DataHH & HP&HCNUMSaleTaxable
(HH) ajshjklagdhh(HH)1-2
(HP)kjhkjhghjghjghp(HP)22-
(HH)gfghfgd(HH)1-4
(HP)fdghdghdhgdg(HP)24-
(HH)gjgjhjhjhhj(HH)1-38
(HP)fdgdgggfggfg(HP)26-
(HP)adsgfdghdgdg(HP)27-
sadffffggf-35-
(HC)fgfgfgfgfgf(HC)49-
(HC)sdsdfsfs(HC)416-
fdtdfghdhfg-534-
sadasd-63-
sadasd-634-
asdasd-743-
adasd-843
DavidŠtorek
Contributor III

Re: Sub String

Nice . And what is input? What should say NUM,Taxable. What is wrong with imput you are getting?  I would realy like to help you and fot that purpouse I need a little bit more specific info

rajeshqvd
Contributor

Re: Sub String

In Put

Data

Sale
(HH) ajshjklagdhh
(HP)kjhkjhghjghjghp2
(HH)gfghfgd
(HP)fdghdghdhgdg4
(HH)gjgjhjhjhhj
(HP)fdgdgggfggfg6
(HP)adsgfdghdgdg7

Out Put

DataSaleForOUT PUT
(HH) ajshjklagdhh(HH)2
(HP)kjhkjhghjghjghp2(HP)0
(HH)gfghfgd(HH)4
(HP)fdghdghdhgdg4(HP)0
(HH)gjgjhjhjhhj(HH)13
(HP)fdgdgggfggfg6(HP)0
(HP)adsgfdghdgdg7(HP)0

Re: Sub String

Can you explain how OUTPUT can be done?

rajeshqvd
Contributor

Re: Sub String

Actually my data like this i want to sum (HP) if any (HC)  and assign value to (HH) and remove (HP) and (HC) values this is my requirement ....first table is my actual data need to get FOR and OUTPUT fields in script side like second table.

In Put

Data

Sale
(HH) ajshjklagdhh
(HP)kjhkjhghjghjghp2
(HH)gfghfgd
(HP)fdghdghdhgdg4
(HH)gjgjhjhjhhj
(HP)fdgdgggfggfg6
(HP)adsgfdghdgdg7

Out Put

DataSaleForOUT PUT
(HH) ajshjklagdhh(HH)2
(HP)kjhkjhghjghjghp2(HP)0
(HH)gfghfgd(HH)4
(HP)fdghdghdhgdg4(HP)0
(HH)gjgjhjhjhhj(HH)13
(HP)fdgdgggfggfg6(HP)0
(HP)adsgfdghdgdg7(HP)0