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 product A, 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 B, 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.

Anyone can show me how to do this in Qlikview?

Thank you.

[niclaz79]

Hi,

This works for me:

LOAD [Product Category],

    Model,

    [Serial Number],

    [Occurrence Month],

    if([Product Category] = 'A',

      MakeDate('20' & Left([Serial Number],2),Right(Left([Serial Number],3),1),'01'),

      MakeDate('201' & Left([Serial Number],1),Right(Left([Serial Number],4),1),'01')

      ) as Date

FROM

[...\Sample Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Just get the file location updated and you can use the above directly.

[wendytham]

Hi Niclas,

Thanks for your suggestion.

How if the month is represented by different number/alphabet in the serial number as below?

1=Jan

2=Feb

3=Mar

4=Apr

5=May

6=Jun

7=Jul

8=Aug

9=Sep

O=Oct

N=Nov

D=Dec

[niclaz79]

Hi,

You’re right it only works from 1-9 at this point. I would create a mapping table

mDate:

Mapping

LOAD * Inline [

Input, Output

'O', 10

'N', 11

'D', 12

];

And then use the map in the expression for month in the MakeDate function:

    if([Product Category] = 'A',

      MakeDate('20' & Left([Serial Number],2),ApplyMap('mDate', Right(Left([Serial Number],3),1), Right(Left([Serial Number],3),1)),'01'),

      MakeDate('201' & Left([Serial Number],1),ApplyMap('mDate', Right(Left([Serial Number],4),1), Right(Left([Serial Number],4),1)),'01')

      ) as Date

This way if it is any of O, N or D it will give 10, 11 or 12 and otherwise it will use the actual number in the serial number.

[wendytham]

Hi,

Ok, understand. How about plotting those charts as shown in the sample chart excel file?

It is possible to create a chart like this?

[niclaz79]

Yes, you can use a pivot table to create a chart like that by using both sets of dates as dimensions.

[wendytham]

How to separate the date into month and year into 2 fields?

[shwethaa]

Use Date function or Month() or Year().