Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wendytham
Contributor II
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
niclaz79
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

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
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

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
Partner - Creator III
Partner - Creator III

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

wendytham
Contributor II
Contributor II
Author

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

shwethaa
Contributor III
Contributor III

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