Extract information from Serial Number and plot duality chart

[wendytham]

Hi All,

 I wish to plot duality chart (sample chart as attached) by using the attached data.

 In the serial number, for Category BC, i would like to extract the first 2 digits as year and 3rd digit as month.

Eg.: 1861212345

18 = Year 2018

6 = June

For product AC, the first digit is year, 4th digit is the month.

Eg.: 8AB513154

8 = Year 2018

5 = May

The extracted month-year from serial number will be the production month.

For both category AC & BC, the serial number for October, November and December is different.

O = October

N = November

D = December

The repair qty is DISTINCT (Count Job#) for each month.

Monthname is the repairing month.

Anyone can show me how to do this in Qlikview?

[chrismarlow]

Hello,

Assuming you are scripting you can do a mapping load & the apply map in a nested if function preceding load on your data, like this;

map_month:
Mapping LOAD * INLINE [
    Key, Month
    1, Jan
    2, Feb
    3, Mar
    4, Apr
    5, May
    6, Jun
    7, Jul
    8, Aug
    9, Sep
    O, Oct
    N, Nov
    D, Dec
];

data:
Load
 If(Category='AC',
  applymap('map_month',mid(Serial#,4,1))&'-201'& left(Serial#,1),
  if(Category='BC',
   applymap('map_month',mid(Serial#,3,1))&'-20'& left(Serial#,2),
   ''
   )
  ) AS ProductionMonth,
 *;LOAD Monthname,
     Category,
     Job#,
     Model,
     Serial#,
     SalesQty
FROM [Duality Chart sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);

I've attached, but only have personal edition so not sure if you will be able to open.

You will also need to work out what to do with your CD's.

Cheers,

Chris.