New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
New Contributor II

Duality Chart

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.

7 Replies
Highlighted
Partner

Re: Duality Chart

Hi,

This works for me:

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.

Highlighted
New Contributor II

Re: Duality Chart

Hi Niclas,

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

Highlighted
Partner

Re: Duality Chart

Hi,

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

mDate:

Mapping

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.

Highlighted
New Contributor II

Re: Duality Chart

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?

Highlighted
Partner

Re: Duality Chart

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

Highlighted
New Contributor II

Re: Duality Chart

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

Highlighted
New Contributor III

Re: Duality Chart

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