Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I am trying to achieve the following but I am not sure what i am missing:
Dataset1:
Incident Num | Problem Description |
1439 | 236715breakdown |
1237 | 487985Shortage |
1777 | 691588Hold |
1742 | 593921Stopped |
1793 | 605891Critical |
1193 | 695587Support |
1606 | 505381breakdown |
1702 | 256265Shortage |
1570 | 278909Hold |
1920 | 236715Stopped |
1544 | 487985Critical |
1199 | 691588Support |
1539 | 593921breakdown |
1344 | 605891Shortage |
1332 | 695587Hold |
1162 | 505381Stopped |
1487 | 256265Critical |
1808 | 278909Support |
1239 | 691588breakdown |
1377 | 593921Shortage |
1129 | 605891Hold |
1560 | 695587Stopped |
1161 | 505381Critical |
1806 | 256265Support |
DataSet2:
Veh No | Status | Line |
M00-236715 | Ongoing | L1 |
M00-487985 | Started | L1 |
M00-691588 | Complete | L1 |
M00-593921 | Complete | L2 |
M00-605891 | Started | L2 |
M00-695587 | Complete | L2 |
M00-505381 | Ongoing | L1 |
M00-256265 | Ongoing | L1 |
M00-278909 | Complete | L2 |
So, I am trying to extract left 6 digits from the problem description from dataset 1 as 'Veh No' and count the number of incidents for each of 'Veh No' . After I get the number of incidents for each Veh No, i want to map to to Dataset2.
My Script:
Dataset1:
Mapping Load Left(Problem Description,6) as Veh No,
Count(Incident Num) as Incidents
From Dataset1
Group by Left(Problem Description,6);
Datset2:
Load Right(Veh No,6) as VehNo,
Status,
Line,
ApplyMap('DataSet1', Right(Veh No,6),'NF') as IncidentCount
From Dataset2;
Results should be:
Veh No | Status | Line | Count |
236715 | Ongoing | L1 | 2 |
487985 | Started | L1 | 2 |
691588 | Complete | L1 | 3 |
593921 | Complete | L2 | 3 |
605891 | Started | L2 | 3 |
695587 | Complete | L2 | 3 |
505381 | Ongoing | L1 | 3 |
256265 | Ongoing | L1 | 3 |
278909 | Complete | L2 | 2 |
However, My result table looks like the following:
Veh No | Status | Line | Count |
236715 | Ongoing | L1 | 1 |
487985 | Started | L1 | 1 |
691588 | Complete | L1 | 1 |
593921 | Complete | L2 | 1 |
605891 | Started | L2 | 1 |
695587 | Complete | L2 | 1 |
505381 | Ongoing | L1 | 1 |
256265 | Ongoing | L1 | 1 |
278909 | Complete | L2 | 1 |
I am not sure what i am missing.
Can someone help ?
Do I need to use Resident Load or Preceding Load.. ..if So, kindly suggest.
Thanks,
Vidya
Hi,
Your data changed a bit, so im changing the code a bit
Dataset:
load
[Incident Num],
mid([Problem Description],7) as [Problem Description],
left([Problem Description],6) as [Problem Number];
LOAD [Incident Num],
[Problem Description]
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',');
Dataset2:
LOAD [Veh No],
Status,
Line
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
left join (Dataset2)
Load
[Problem Number] as [Veh No],
1 as [Count Number]
Resident Dataset;
And for the graph, de-select the "Suppress Zero-Values". This will show the other values, like so:
Hi Vidya,
I used the following code:
load
[Incident Num],
mid([Problem Description],7) as [Problem Description],
left([Problem Description],6) as [Problem Number];
LOAD [Incident Num],
[Problem Description]
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',');
LOAD [Veh No],
SubField([Veh No],'-',2) as [Problem Number],
Status,
Line
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Which got me the result you wanted, as shown below:
Hi Felip,
This answers my problem to some extent.
So, usually I have more data for Veh No in Dataset2 and therefore I will have more ProblemNumbers in DataSet2.
If I use the above logic, it is basing Problem Number coming from Dataset1 as basis and it would avoid some problem numbers form Dataset2.
Example Dataset:2
DataSet2:
Veh No | Status | Line |
236715 | Ongoing | L1 |
487985 | Started | L1 |
691588 | Complete | L1 |
593921 | Complete | L2 |
605891 | Started | L2 |
695587 | Complete | L2 |
505381 | Ongoing | L1 |
256265 | Ongoing | L1 |
278909 | Complete | L2 |
278910 | Complete | L2 |
505382 | Ongoing | L1 |
695588 | Complete | L2 |
Resulting Dataset:
Veh No | Status | Line | Count |
236715 | Ongoing | L1 | 2 |
487985 | Started | L1 | 2 |
691588 | Complete | L1 | 3 |
593921 | Complete | L2 | 3 |
605891 | Started | L2 | 3 |
695587 | Complete | L2 | 3 |
505381 | Ongoing | L1 | 3 |
256265 | Ongoing | L1 | 3 |
278909 | Complete | L2 | 2 |
278910 | Complete | L2 | 0 |
505382 | Ongoing | L1 | 0 |
695588 | Complete | L2 | 0 |
Going by the code you gave me, It will only give the following:
Veh No | Status | Line | Count |
236715 | Ongoing | L1 | 2 |
487985 | Started | L1 | 2 |
691588 | Complete | L1 | 3 |
593921 | Complete | L2 | 3 |
605891 | Started | L2 | 3 |
695587 | Complete | L2 | 3 |
505381 | Ongoing | L1 | 3 |
256265 | Ongoing | L1 | 3 |
278909 | Complete | L2 | 2 |
Any ideas how to fit them in the resulting table?
Thanks,
Vidya
Hi,
Your data changed a bit, so im changing the code a bit
Dataset:
load
[Incident Num],
mid([Problem Description],7) as [Problem Description],
left([Problem Description],6) as [Problem Number];
LOAD [Incident Num],
[Problem Description]
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',');
Dataset2:
LOAD [Veh No],
Status,
Line
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
left join (Dataset2)
Load
[Problem Number] as [Veh No],
1 as [Count Number]
Resident Dataset;
And for the graph, de-select the "Suppress Zero-Values". This will show the other values, like so:
Which chart you are using? Did you uncheck supress 0 values from presentation tab?
Thanks Felip
Hi Vishwa,
I am using Straight Table.
Thanks
Vidya
Glad it helped .