How to get the data loaded in a predetermined format

[nevilledhamsiri]

Dear All

I need the data in the table (vehicle number column) to be loaded in the manner how 4th row number is appeared(WP_SY_2530).Hence all numbers need to be arranged 1st two letters then underscore then the 2nd two letters again underscore & the last four numbers. how this could be done?

Many thanks in advance

Neville

SG-JK-2541	14500
WP-PG-1825	20000
WP_KN-1425	12500
WP_SY_2530	45000
WPLK-1525	22500
WPOP-2852	15000
WPXD8520	25000

[m_woolf]

left(purgechar(YourField,'_-'),2) & '_' & mid(purgechar(YourField,'_-'),3,2) & '_' & mid(purgechar(YourField,'_-'),5,4) as NewField

[sunny_talwar]

May be like this

Left(PurgeChar(FieldName, '-_'), 2) & '_' & Mid(PurgeChar(FieldName, '-_'), 3, 2) & '_' & Right(PurgeChar(FieldName, '-_'), 4) as FieldName

[nevilledhamsiri]

Thank Sunny. It works fine!

Thank M W too for your answer

SG-JK-2541	SG_JK_2541	14500
WP-PG-1825	WP_PG_1825	20000
WP_KN-1425	WP_KN_1425	12500
WP_SY_2530	WP_SY_2530	45000
WPLK-1525	WP_LK_1525	22500
WPOP-2852	WP_OP_2852	15000
WPXD8520	WP_XD_8520	25000

Neville

[CarmenReilly]

Hi Neville!

Glad Sunny and M were able to help you get this sorted out!

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

-Carmen

[nevilledhamsiri]

Hi Sunny, m w

Please see the table & develop the already posted formula to cover of the different length of vehicle numbers. As can be seen some numbers are not formatted as required

Also due to different date format in the excel purchase Month are not coming for some cases were date appeared as (31-05-2015) etc. Kindly help me to correct this too

Many thanks

Neville 

[nevilledhamsiri]

Please see the attachment

[sunny_talwar · Accepted solution]

Try the attached

LOAD [BR_CODE (AS PER OUR REORDS)],

[Inward Date],

Date(Alt(Date([Purchased Date]), Date#([Purchased Date], 'DD-MM-YYYY'))) as [Purchased Date],

Month(Alt(Date([Purchased Date]), Date#([Purchased Date], 'DD-MM-YYYY'))) as purchased_month,

[Asset Ref. No.],

[Asset ID No.],

Left(PurgeChar([Asset ID No.] , '-_ '), 2) & '_' & Mid(PurgeChar([Asset ID No.]  , '-_ '), 3, Len(PurgeChar([Asset ID No.]  , '-_ '))-6) & '_' & Right(PurgeChar([Asset ID No.] , '-_ '), 4) as FieldName,

Asset_Description,

[Dep. Comm ence Date],

[Dep. Rate],

[Opening Balance],Additions,

Inwards,

[Opening Balance1],

[Current Year Value],

[Written Down Value],

[Inward Transfer],

[Outward Transfer],

[Sales Value],

[Profit/ Loss Value],

[T.R. Value],

[Net Written Down Value],

F21,

F22,

F23,

F24,

[date parked]

FROM [SUMMERIZED_VEHICLE_REGISTER(AS AT NOV_2017) (with Br Confirmations).xlsx]

(ooxml, embedded labels, table is Master);

[nevilledhamsiri]

Vehicle number issue was resolved!. Thank you very much. But for the date format once applied your format just below the purchase date in the front end, an error comes. Could you please see to that

[sunny_talwar]

Not sure I understand