Profile picture for user ggs

May be someone here can help to untwist my brain. I could not find a solution up to now.

I have 2 tables and want to get a cross join. Sample:

Data Feed 1 returns:

SQUARE    08  1234

CALC         08   5678

 

Data Feed 2 returns:

07

08

09

I want to construct the following output within a Data Feed:

SQUARE 07            0

SQUARE 08            1234

SQAURE 09           0

CALC     07            0

CALC     08           5678

CALC     08           0

I didn't manage to achieve this with the Combine or Concatenate operators. Any idea?

by Donald Dillon
Posted on Sun, 11/20/2011 - 20:11

Georg,

I have a similar situation. 

We have projects started by different groups.  Each project has a start date (rounded to month)

Not all groups started a project every month

The results we get now

x-axis=month

y-axis=count of projects

the problem is that if a month had no projects the x-axis condenses and simply 'hides the gap'.

We want to display all months in the range and then of course the number of projects will be 0.  I envision doing this somehow by extracting the min and max months in the records, generating the records to fill the gap and to give each month a record value of 0.

then I will join where the records with real values will be supplemented by the records with values of 0 to dynamically create a 'full' data set to be presented.

Do you see the similarities?  I would be interested in any solution you come up with as it may be applicable for my scenario.

Thanks

Donald

 

0
by Georg Schmitt Author
Posted on Mon, 11/21/2011 - 07:53

Hi Donald,

yes, my issue is the same than yours. I have a time range (e.g month) and I have transactions. But there are month, where no transaction happens.

My issue goes even further. My time range may be year, month, day, hour, minute, second. I can even handle that, see below. I can also handle the days, depending on the month. But when I want to differenciate between transaction from application A and application B, I came to the point, where I think I need to do a cross join.

My solution for one dimension (e.g. month and transaction only): I have made a manual data source with one entry for each month (01 - 12) and transactions set to 0. Then I use the "Combine Data Feeds" operator and join real project data with the manual data feed. Afterwards I use the "Aggregation" operator to use the maximum values for the transactions only. This means the month - transaction pair, where the transactions are 0, will be removed.

0

Featured achievement

Rookie
Say hello to the ARIS Community! Personalize your community experience by following forums or tags, liking a post or uploading a profile picture.
Recent Unlocks
  • SS
  • MZ
  • Profile picture for user kbiront
  • Profile picture for user Tony Iliev
  • Profile picture for user amandeep.7.singh
  • PacMan

Leaderboard

|
icon-arrow-down icon-arrow-cerulean-left icon-arrow-cerulean-right icon-arrow-down icon-arrow-left icon-arrow-right icon-arrow icon-back icon-close icon-comments icon-correct-answer icon-tick icon-download icon-facebook icon-flag icon-google-plus icon-hamburger icon-in icon-info icon-instagram icon-login-true icon-login icon-mail-notification icon-mail icon-mortarboard icon-newsletter icon-notification icon-pinterest icon-plus icon-rss icon-search icon-share icon-shield icon-snapchat icon-star icon-tutorials icon-twitter icon-universities icon-videos icon-views icon-whatsapp icon-xing icon-youtube icon-jobs icon-heart icon-heart2 aris-express bpm-glossary help-intro help-design Process_Mining_Icon help-publishing help-administration help-dashboarding help-archive help-risk icon-knowledge icon-question icon-events icon-message icon-more icon-pencil forum-icon icon-lock