Sub String

[rajeshqvd]

Hi All Please need help on below requirement in Qlik Sense

Data	Sale
(HH) ajshjklagdhh
(HP)kjhkjhghjghjghp	2
(HH)gfghfgd
(HP)fdghdghdhgdg	4
(HH)gjgjhjhjhhj
(HP)fdgdgggfggfg	6
(HP)adsgfdghdgdg	7

Out put

Data	Sale	For	OUT PUT
(HH) ajshjklagdhh		(HH)	2
(HP)kjhkjhghjghjghp	2	(HP)	0
(HH)gfghfgd		(HH)	4
(HP)fdghdghdhgdg	4	(HP)	0
(HH)gjgjhjhjhhj		(HH)	13
(HP)fdgdgggfggfg	6	(HP)	0
(HP)adsgfdghdgdg	7	(HP)	0

[rajeshqvd · Accepted solution]

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

Data	HH & HP&HC	NUM	Sale	Taxable
(HH) ajshjklagdhh	(HH)	1	-	44
(HP)kjhkjhghjghjghp	(HP)	2	2	-
(HH)gfghfgd	(HH)	1	-	44
(HP)fdghdghdhgdg	(HP)	2	4	-
(HH)gjgjhjhjhhj	(HH)	1	-	44
(HP)fdgdgggfggfg	(HP)	2	6	-
(HP)adsgfdghdgdg	(HP)	2	7	-
sadffffggf	-	3	5	-
(HC)fgfgfgfgfgf	(HC)	4	9	-
(HC)sdsdfsfs	(HC)	4	16	-
fdtdfghdhfg	-	5	34	-
sadasd	-	6	3	-
sadasd	-	6	34	-
asdasd	-	7	43	-
adasd	-	8	43	-

[ashwin_r]

Try This,

Load

Data,

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

Sale.

*

from.........

Regards,

Ashwin.R

[DavidŠtorek]

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]

out put should be like this

Data	HH & HP&HC	NUM	Sale	Taxable
(HH) ajshjklagdhh	(HH)	1	-	2
(HP)kjhkjhghjghjghp	(HP)	2	2	-
(HH)gfghfgd	(HH)	1	-	4
(HP)fdghdghdhgdg	(HP)	2	4	-
(HH)gjgjhjhjhhj	(HH)	1	-	38
(HP)fdgdgggfggfg	(HP)	2	6	-
(HP)adsgfdghdgdg	(HP)	2	7	-
sadffffggf	-	3	5	-
(HC)fgfgfgfgfgf	(HC)	4	9	-
(HC)sdsdfsfs	(HC)	4	16	-
fdtdfghdhfg	-	5	34	-
sadasd	-	6	3	-
sadasd	-	6	34	-
asdasd	-	7	43	-
adasd	-	8	43

[DavidŠtorek]

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]

In Put Data	Sale
(HH) ajshjklagdhh
(HP)kjhkjhghjghjghp	2
(HH)gfghfgd
(HP)fdghdghdhgdg	4
(HH)gjgjhjhjhhj
(HP)fdgdgggfggfg	6
(HP)adsgfdghdgdg	7

Out Put

Data	Sale	For	OUT PUT
(HH) ajshjklagdhh		(HH)	2
(HP)kjhkjhghjghjghp	2	(HP)	0
(HH)gfghfgd		(HH)	4
(HP)fdghdghdhgdg	4	(HP)	0
(HH)gjgjhjhjhhj		(HH)	13
(HP)fdgdgggfggfg	6	(HP)	0
(HP)adsgfdghdgdg	7	(HP)	0

[Anil_Babu_Samineni]

Can you explain how OUTPUT can be done?

[rajeshqvd]

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)kjhkjhghjghjghp	2
(HH)gfghfgd
(HP)fdghdghdhgdg	4
(HH)gjgjhjhjhhj
(HP)fdgdgggfggfg	6
(HP)adsgfdghdgdg	7

Out Put

Data	Sale	For	OUT PUT
(HH) ajshjklagdhh		(HH)	2
(HP)kjhkjhghjghjghp	2	(HP)	0
(HH)gfghfgd		(HH)	4
(HP)fdghdghdhgdg	4	(HP)	0
(HH)gjgjhjhjhhj		(HH)	13
(HP)fdgdgggfggfg	6	(HP)	0
(HP)adsgfdghdgdg	7	(HP)	0

[Anil_Babu_Samineni]

Again, Not sure. I apologies for not helping. May be check this?

https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/StringFunctions/SubFiel...

https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/StringFunctions/SubStri...