Extract information from Serial Number and plot duality chart

wendytham — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Extract information from Serial Number and plot duality chart

chrismarlow — Thu, 15 Nov 2018 15:41:10 GMT

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.

topic Re: Extract information from Serial Number and plot duality chart in QlikView

Extract information from Serial Number and plot duality chart

Re: Extract information from Serial Number and plot duality chart