topic How to get the data loaded in a predetermined format in QlikView

How to get the data loaded in a predetermined format

nevilledhamsiri — Thu, 26 Apr 2018 18:22:38 GMT

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

Re: How to get the data loaded in a predetermined format

m_woolf — Thu, 26 Apr 2018 18:27:41 GMT

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

Re: How to get the data loaded in a predetermined format

sunny_talwar — Thu, 26 Apr 2018 18:28:18 GMT

May be like this

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

Re: How to get the data loaded in a predetermined format

nevilledhamsiri — Thu, 26 Apr 2018 18:37:57 GMT

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

Re: How to get the data loaded in a predetermined format

CarmenReilly — Fri, 27 Apr 2018 17:04:30 GMT

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

Re: How to get the data loaded in a predetermined format

nevilledhamsiri — Fri, 27 Apr 2018 19:02:18 GMT

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

Re: How to get the data loaded in a predetermined format

nevilledhamsiri — Fri, 27 Apr 2018 19:04:57 GMT

Please see the attachment

Re: How to get the data loaded in a predetermined format

sunny_talwar — Fri, 27 Apr 2018 19:15:07 GMT

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

Re: How to get the data loaded in a predetermined format

nevilledhamsiri — Fri, 27 Apr 2018 19:53:38 GMT

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

Re: How to get the data loaded in a predetermined format

sunny_talwar — Fri, 27 Apr 2018 20:51:28 GMT

Not sure I understand

Re: How to get the data loaded in a predetermined format

nevilledhamsiri — Sat, 28 Apr 2018 01:43:59 GMT

Sorry, an error on my side!

Thanks a lot