Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear guys,
I have a sample number of 0000123456000.
So how can I get the number only 123456?
Please any tips from anyone.
@Prudhviqliks I encountered similar situation, but the number had 0 in between other digits (example 000012304000 and i was asked to extract 5 digit 12304 )
below is my approach. I have attached the output i achieved
test:
load * inline [
testnum
00012304000
00224580000
0000456780
012005000
];
for j = 0 to NoOfRows('test')
let vtest = peek('testnum',$(j),'test');
let vLenTest = len(peek('testnum',$(j),'test'));
for i = 0 to $(vLenTest)
if (left('$(vtest)',$(i))>0) then
temp:
Load
mid($(vtest),$(i),5) as extract1
AutoGenerate 1;
let i = $(vLenTest);
end if;
next i;
next j;
for k =0 to NoOfRows('test')
Output:
Load
peek('testnum',$(k),'test') as testnum,
peek('extract1',$(k),'temp') as output1
AutoGenerate 1;
next k;
drop tables temp,test;
A clever solution I have seen in this forum for removing leading and trailing zeros while maintaining embedded zeros is:
Replace(Trim(Replace(testnum, '0', ' ')), ' ', '0')
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
purgechar(number,'0')
keepchar(number,'123456789')
...
Thank you @mikaelsc for quick response and solution , great 👌
@Prudhviqliks I encountered similar situation, but the number had 0 in between other digits (example 000012304000 and i was asked to extract 5 digit 12304 )
below is my approach. I have attached the output i achieved
test:
load * inline [
testnum
00012304000
00224580000
0000456780
012005000
];
for j = 0 to NoOfRows('test')
let vtest = peek('testnum',$(j),'test');
let vLenTest = len(peek('testnum',$(j),'test'));
for i = 0 to $(vLenTest)
if (left('$(vtest)',$(i))>0) then
temp:
Load
mid($(vtest),$(i),5) as extract1
AutoGenerate 1;
let i = $(vLenTest);
end if;
next i;
next j;
for k =0 to NoOfRows('test')
Output:
Load
peek('testnum',$(k),'test') as testnum,
peek('extract1',$(k),'temp') as output1
AutoGenerate 1;
next k;
drop tables temp,test;
A clever solution I have seen in this forum for removing leading and trailing zeros while maintaining embedded zeros is:
Replace(Trim(Replace(testnum, '0', ' ')), ' ', '0')
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
Great @ratnadeep , expecting your contribution on this community more in future, thank you.
Sure @rwunderlich , thank you for your solution 🤝.
Great help @rwunderlich !!! thank you very much.