Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
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?
Yes, you can use a pivot table to create a chart like that by using both sets of dates as dimensions.
How to separate the date into month and year into 2 fields?
Use Date function or Month() or Year().