topic Re: Duality Chart in QlikView

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 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.

Re: Duality Chart

niclaz79 — Wed, 03 Oct 2018 07:38:51 GMT

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.

Re: Duality Chart

wendytham — Wed, 03 Oct 2018 07:44:27 GMT

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

Re: Duality Chart

niclaz79 — Wed, 03 Oct 2018 07:51:18 GMT

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.

Re: Duality Chart

wendytham — Wed, 03 Oct 2018 08:07:30 GMT

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?

Re: Duality Chart

niclaz79 — Wed, 03 Oct 2018 08:23:13 GMT

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

Re: Duality Chart

wendytham — Thu, 04 Oct 2018 02:30:17 GMT

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

Re: Duality Chart

shwethaa — Thu, 04 Oct 2018 10:30:27 GMT

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