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: 
poluvidyasagar
Creator II
Creator II

Count of a dimension during Load?

Hi ,

I am trying to achieve the following but I am not sure what i am missing:

Dataset1:

  

Incident NumProblem Description
1439236715breakdown
1237487985Shortage
1777691588Hold
1742593921Stopped
1793605891Critical
1193695587Support
1606505381breakdown
1702256265Shortage
1570278909Hold
1920236715Stopped
1544487985Critical
1199691588Support
1539593921breakdown
1344605891Shortage
1332695587Hold
1162505381Stopped
1487256265Critical
1808278909Support
1239691588breakdown
1377593921Shortage
1129605891Hold
1560695587Stopped
1161505381Critical
1806256265Support

DataSet2:

Veh NoStatusLine
M00-236715OngoingL1
M00-487985StartedL1
M00-691588CompleteL1
M00-593921CompleteL2
M00-605891StartedL2
M00-695587CompleteL2
M00-505381OngoingL1
M00-256265OngoingL1
M00-278909CompleteL2

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 NoStatusLineCount
236715OngoingL12
487985StartedL12
691588CompleteL13
593921CompleteL23
605891StartedL23
695587CompleteL23
505381OngoingL13
256265OngoingL13
278909CompleteL22

         

However,  My result table looks like the following:

   

Veh NoStatusLineCount
236715OngoingL11
487985StartedL11
691588CompleteL11
593921CompleteL21
605891StartedL21
695587CompleteL21
505381OngoingL11
256265OngoingL11
278909CompleteL21

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

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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:

sample.png

View solution in original post

7 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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:

sample.png

poluvidyasagar
Creator II
Creator II
Author

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 NoStatusLine
236715OngoingL1
487985StartedL1
691588CompleteL1
593921CompleteL2
605891StartedL2
695587CompleteL2
505381OngoingL1
256265OngoingL1
278909CompleteL2
278910CompleteL2
505382OngoingL1
695588CompleteL2

Resulting Dataset:

Veh NoStatusLineCount
236715OngoingL12
487985StartedL12
691588CompleteL13
593921CompleteL23
605891StartedL23
695587CompleteL23
505381OngoingL13
256265OngoingL13
278909CompleteL22
278910CompleteL20
505382OngoingL10
695588CompleteL20

Going by the code you gave me, It will only give the following:

   

Veh NoStatusLineCount
236715OngoingL12
487985StartedL12
691588CompleteL13
593921CompleteL23
605891StartedL23
695587CompleteL23
505381OngoingL13
256265OngoingL13
278909CompleteL22

Any ideas how to fit them in the resulting table?

Thanks,

Vidya

felipedl
Partner - Specialist III
Partner - Specialist III

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:

sample.png

vishsaggi
Champion III
Champion III

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

poluvidyasagar
Creator II
Creator II
Author

Thanks Felip

poluvidyasagar
Creator II
Creator II
Author

Hi Vishwa,

I am using Straight Table.

Thanks
Vidya

felipedl
Partner - Specialist III
Partner - Specialist III

Glad it helped .