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

[srikanth99]

Hi Team,

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

[qlikviewwizard]

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);

[srikanth99]

There are still names displaying in the vehicle number.

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

[agomes1971]

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]

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]

Hi,

please see the attached.

HTH

André Gomes