Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
srikanth99
Contributor III
Contributor III

Need to seperate vehicle no and driver name from field, Lafarge should not come

Hi Team,

 

               After separting driver name and vehicle number we should calculate the no of kilometres unknown driver has travelled.

5 Replies
qlikviewwizard
Master II
Master II

Hi srikanth99

Please check this.

LOAD*,SubField(Name,'_',1) as Vehical_Number,

Capitalize(replace(PurgeChar(SubField(Name,'_',2),'1234567890'),'/','')) as Driver_Name;

LOAD Date,

     Name,

     F3,

     Distance

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Capture.PNG

Capture1.PNG

srikanth99
Contributor III
Contributor III
Author

There are still names displaying in the vehicle number.

I had tried, wen you drag it down you can observe names as well

agomes1971
Specialist II
Specialist II

Please test with this script:

Test:

LOAD*,

IF(index( Name, '_' ) = 6 or index( Name, '_' ) = 7 or index( Name, '_' ) = 8 or  index( Name, '_' ) = 9 ,SubField(Name,'_',1)

,  IF(index( Name, '-' ) = 6 or index( Name, '-' ) = 8 or index( Name, '-' ) = 9 ,SubField(Name,'-',1))) as Vehical_Number,

IF(index( Name, '_' ) = 6 or  index( Name, '_' ) = 7 or index( Name, '_' ) = 8 or  index( Name, '_' ) = 9 ,Capitalize(replace(PurgeChar(SubField(Name,'_',2),'1234567890'),'/',''))

, IF(index( Name, '-' ) = 6 or index( Name, '-' ) = 8 or index( Name, '-' ) = 9, Capitalize(replace(PurgeChar(SubField(Name,'-',2),'1234567890'),'/','')))) as Driver_Name;

LOAD Date,

     Name,

     F3,

     Distance

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

HTH

André Gomes

srikanth99
Contributor III
Contributor III
Author

Hi Gomes,

                  Please look at the remaining, these should be added in vehicle name and then should be seperated as well.  Lafarge and colon should not come.

 

ALP 931 - Monde Fwambo
ALP 933 Simon Nkumbi CHI
Lafarge - ABZ 7571 Milandu 0978381816
Lafarge - ABZ 7572 Nyirenda 0972009045
Lafarge - ALB 8366 Musa Evans 0965517909
Lafarge - ALD 9567 Zulu Collins 0977 587549
Lafarge - ALD 9568 Pingilani Phiri 0977 545599
Lafarge - ALE 3843 Mukupa Blackwell 0962 781778
Lafarge - ALV 8783 Sapau 0977156096
Suspended - ABL 8320
Suspended - ABZ 7565
Suspended - ABZ 7566
Suspended - ALD 9564
BCA6720_unknown driver
BCA6720_unknown driver
BCA6720_unknown driver
BCA6720_unknown driver
agomes1971
Specialist II
Specialist II

Hi,

please see the attached.

HTH

André Gomes