Count of a dimension during Load?

[poluvidyasagar]

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

[felipedl]

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:

[poluvidyasagar]

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

[felipedl · Accepted solution]

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:

[vishsaggi]

Which chart you are using? Did you uncheck supress 0 values from presentation tab?

[poluvidyasagar]

Thanks Felip

[poluvidyasagar]

Hi Vishwa,

I am using Straight Table.

Thanks
Vidya

[felipedl]

Glad it helped .