0

Hi I have a mobile application in that MobApp, customer can see the information about

money,news,forecast,advice and can download the app in that case i need to find out the count for how

many times customers visited money,news,forecast,advice and through which ref code customer

downloaded app separately on daily basis or download date

The log table consists of columns like

id user_key url_accessed parameters timestamp .. ......... ............ .......... ............

1 sdhbhjbh /user/money mobile_no=9999999 2015-01-08 13:06:33

2 cnbjknjk /user/news user_key=534d2135 2014-09-06 26:02:25

3 l;mjl;k; /download ref=abcd 2014-10-22 07:37:57

Here I have to find how many times user accessed the url daily on date basis and count for accessing

the url of money,news,forecast,advisory,download

Here I am using SQL Server with Pentaho data integration

For above issue i have to create separate new_table for url_accessed with columns

money,news,weather,forecast,download. Here, i need to populate the data from log_table to new_table

where the columns money,news,weather,forecast,download are in this columns the data has to come count

for money,count for news and for all columns

using pentaho data integration. How can i do this, help me in this task

SRI
  • 531
  • 1
  • 4
  • 11

2 Answers2

0

This might helps you 

Select user,URL,count(URL)
from table
where date=getdate() 
group by URL,user
koushik veldanda
  • 1,079
  • 10
  • 23
  • above u have given query for count for columns that is fine but at the same time i mentioned in my question that i need to populate count data to specified columns as well and that data has to go another table in another table the column names are money,news,weather,advisory,download – SRI Jan 14 '15 at 09:43
0

Using PDI, you can try first of all to use the sql queries(as mentioned above and in here) to get the distinct count of the users.

enter image description here

Secondly, you need to find out from the url, what are the columns that are getting accessed. For e.g.; if an user has accessed "/user/money" then his tab-access would be "money". The sum count of your data in the new-table need to be in this table.

Third, use "Memory Group By" step to get the total sum count of the tab's accessed. Check the image below. [Note: i have only used minimal parameter, u can use users as another condition]

Fourth, Load this data into some staging table and Finally in a separate transformation, query this staging table (use case statements) and load the data as per your required format/table.

I have given a very basic sample in here. Please check it out.

Hoping this will help you out :)

enter image description here

Community
  • 1
  • 1
Rishu Shrivastava
  • 3,745
  • 1
  • 20
  • 41
  • In Modified Java Script Value i don't have much knowledge of xml, in place of ID_BATCH,CHANNEL_ID what have to mention – SRI Jan 15 '15 at 09:00
  • @SRI copy the xml code and save it using the .ktr extension. Then open it in PDI. u can see the code. :) Use notepad++ or some editor.. and i have used Pentaho 5.0.0 version – Rishu Shrivastava Jan 15 '15 at 09:44
  • in above diagram in place of tab_accessed i have to get money,forecast,news as columns for that where can i make changes – SRI Jan 16 '15 at 06:36
  • @SRI read the fourth and final step in the solutions. Load the tab_accessed data into a seperate staging table and then sql the data in the next transformation. I have told you to create a separate ktr for this. – Rishu Shrivastava Jan 16 '15 at 06:45