Need some help in structuring data in wires

The easiest way to do what you are talking about is to have the mashup choose one of 4 different filters based on the user's choice of day, week, month or quarter -- so that the results of the mashup have only those records you want to include in the graph.

You can do this easily in EMML and it can be done in Wires also using execute conditions. Are you working in EMML directly or in Wires?

I am working in Wires , could you please direct me on how to implement this ?

Sure. I'm attaching a screen shot of a mashup in Wires that I dummied up based on your description and the rest of these instructions will refer to that. 

1. You have a SQL block, I'm assuming for your SQL query in Wires. If not, the docs on using the SQL block are at http://www.jackbe.com/prestodocs/v3.0/wires/sqlAction.html. Or you can click help from the SQL block properties. Be sure to run your SQL block to verify that you get the results you want.

2. Add an Input block to the mashup. This is in the Core category of the Blocks menu, so simply drag it into the canvas. The datatype of string is fine, but add a default of "day" or "Day". However you want the values that users will select to be. Note that the actual values that users will select for the App based on this mashup will be defined when you create the App -- this input is just to let users choose and pass that into the mashup.

3. Add a Filter block to the canvas (again in Core blocks) and connect the SQL block to Filter. Fill in the Filter properties to filter for records that are "day" records. If you need help with this, click help in the Filter block properties to see how to set the filter condition. Then run the Filter block and verify that the output of Filter is just the Day records.

4. In the Filter block properties, click on the Advanced Properties tab (at the bottom). In the Execute Condition fields, you are going to add a condition that the value from the Input block matches "day". Basically, what this does is say that the filter will only be run if the user has chosen "day" to filter on. After you add the Execute Condition, run the Filter block again and make sure that you only see "day" records.

5. Repeat steps 3 and 4 to add a Filter block, with Execute condition when the input is "week", "month" and "quarter". So you will have 4 different filter blocks, one for each filter choice of the user, all connected to the SQL block. You can change the default value of the Input block to "week", "month" and "quarter" in order to run and test each of these filters.

6. Add a Merge block and connect all four of your Filter blocks to Merge.

7. If you need to do anything else to the data, add whatever blocks you need after Merge and then connect the final block to Output. Test the mashup, changing the default value of Input as needed.

8. Once your ok with this, save the mashup and turn it on.

9. Open the new mashup's artifact page (typically you are prompted when you save). You should see the input parameter, run the mashup at least once and then add the chart view you need.

10. Create a basic App from this mashup (click Create App). In the wizard, select the mashup input parameter and Allow User Input, make it required and set the Possible Values property with the exact values that you used in your mashup for the execute conditions. Complete the App from there just like you would with any App.

What happens when you use the App is that the input for the filter causes only one of the Filter blocks in the mashup to run. Merge makes sure that whichever filter runs gets output. And the chart only has day, week , month or quarter data to use.

Hi Mitchell

Thank you for the detailed explanation , here is the screen cast video , i followed the steps , as given above , i could see the data at the sql level but when i put it in the filter i dont see, i am actually not sure if my data from sql is as it is supposed to be to fit in what we are trying to achive here ....

Please take a look at the video , i am just trying for date, which is day and week for now.....

http://screencast.com/t/hnyd4iDES

Thank you

Thanks for the video. I have some questions for you, because it looks like what you are trying to do is somewhat more complicated than I understood. Your SQL data has a date column by the looks of it.

When you say you want to filter for "day," do you want users to see only data for today's date? or are you expecting them to enter a specific date that they want to see?

Similarly for "week", do you want users to see data for the last 7 days? or select a specific calendar week?

Based on this, it seems like you need to be able to either categorize your SQL data as belonging to "day" or "week" -- or calculate the actual ranges of dates that corresponds to "day" and "week". You need this in order to set of the Filter blocks to give the right results.

So let's try to get the Filter's to work first, without defining execute conditions.

Hi Sara

Yes i want to categorize the data by the date values , when the users picks up day or date from input drop down list the graph should display by each day , for example mar 1 - mar 6 , all days displayed on x axis , if he picks week from the drop down list then 1 week should display  which is feb 28(monday of the week from mar 1 - mar6) , basically we are summarizing the data.

I have modified the mashup now to pull data summarized data by Week and Date , please take a look at the video and let me know how we can proceed from now , i got 2 sql blocks now , and just tried to see date data from filter but i could not see any data

http://screencast.com/t/82hrG4AAve

Please Help

Thanks

I think I see what you are doing, but let me summarize what I think I see to make sure we're talking about the same thing.

1. You have a start date and end date as inputs to the mashup and some blocks that use those to determine the "main day" or "main week".

2. You use "main day" or "main week" as input to 2 separate SQL blocks that each do a query.

   a. The Day SQL block returns data for individual days for the time period specified.

   b. The Week SQL block returns data for the time period summarized for that week. Not sure if this can have multiple weeks, but it probably doesn' matter.

I wasn't clear if you are getting the correct data from each of these SQL queries. Assuming you are, this seems like a great start.

And if so, it changes the strategy of how to do what you want. Since you are using different SQL queries to handle retrieving the data in the different forms you need, you do not need filter blocks to filter out the unwanted data. What you need is simply to only execute the correct SQL query for the type of query that users choose.  The type of query that users choose determines which SQL query executes. Try this:

1. Perhaps change the label for the Day Filter Type input so you're clear about what this is, which in the input that determines which type of query to run. I'm going to call that Query Type Input.

2. Remove the filter block.

3. On the Day SQL block, add an execution condition that compares the value of the Query Type Input block to "Date" (at least that looks like what you are using). So if users select Date as the query type, then this SQL query is the one that runs.

4. On the Week SQL block, add an execution condition that compares the value of the Query Type Input block to "Week"(or whatever the correct value should be). If that is what users choose, this is the query that runs. Test your SQL blocks at this point to make sure they run if the input parameter is the right value.

5. Add a Merge block and connect both the Day SQL block and the Week SQL block to this. Merge will contain only the results from the query that actually runs.

Do anything else you need to at this point and then connect the final results to the Output block.

Could you please take a look at this video , i think i am doing some mistake while giving the execute condition , i am able to get the data for date avlues , but not Week values , when i give Week as an input parameter i get all the dates data as well as Week data , could you please let me know what is that am doing worng here ....

http://screencast.com/t/S1XzJosbYV

Thank you ...

Couple of things i noticed here ,

If we are having the merge execute condition for each of the sql block , as by default we are giving querytype input block value as Date , so it is showing Date values , While creating the mashup i can give only one value as xaxis , so as we can see only Date column , we will give Date on the X axis , i just re did the whole thing for Week , i.e Week as QeuryInput Value which gave me week values on X axis, so obvioulsly it is giving the column what we specify as Xaxis ,and when i entered "Date" in Input filter , it showed nodata , thats because the column name i specified in the graph to show on x axis is Week not Date ......and there is no way we can give queryinputvalues to display on Xaxis ..correct ?

And even the sort order as i can only sort on one column that would be the default value given in the QueryType input ...

So how would we be able to allow dynamic selections of daytype in the App ?

Hope i tried to explain what am i am looking into...please let me know if you did not get my point here ...

Thank you

Sorry, I'm having trouble following the screencasts. So, can you post a couple of things for me because I'm not sure I can answer the questions without actually seeing the data: 

1. A small sample of the results for the query for Date.

2. A small sample of the results for the query for Week.

If you cannot get samples of the data easily, can you do this:

a. Run one of the query blocks.

b. Maximize the bottom panes in Wires (there is a maximize, minimize, and normal button in the toolbar in the right top corner of this pane).

c. Change the view of the results pane to Tree. You may be to run the Query block again here to get the tree view to show data (flip back and forth between minimized/maximized to get to the canvas)

d. Expand the tree view so that you see the columns of the query results and take a picture of this.

e. Switch to the grid view and take a picture with the data showing

f. Post the pictures here.

3. The EMML for your mashup. This you can get by clicking View EMML in Wires and then simply copying and pasting the code to a text file. Save the file and attach here.

Thanks,

Hi Sara

Actually i wanted you to check if i am following the right way to do , there is a scroll down on the right hand side of the video by which you can pull down and see all you wanted to see above , i am attaching the emml , please let me know if you cannot find the sample data in my video ..

Thank you ...

Attached the sample data , could you please take a look at it , and see if that helps .

I have mocked up a mashup in Wires using your sample data, at least as well as I understand what you want right now. Here is a screencast showing each block in the mashup:

http://www.screencast.com/users/tim98210/folders/prestodocs%20v3.0/media/b2c94774-a8a6-4770-914f-55d42c07d0d0

I'm also attaching a screen shot of the Wires mashup with some notes to clarify. The mashup has two mashables which are basically equivalent to your SQL query blocks (I mocked them up in a spreadsheet with your sample data). The MDCQueryDaily block has an execute condition of: 

Query Type  Matches  Date -- basically comparing the value of the Query Type input to the string "Date"

Similarly MDCQueryWeekly has an execute condition of: 

Query Type Matches Week

After each of the queries, there is a Transformer block. I used this to add one field named xAxis. For the Daily query, this new field is populated with the same data as Date. For the Weekly query, this new field is populated with the data data as Week. The point is to get one column, with one name that has the valus you want to use in your chart on the X axis.

I used Data Decorator to add "Date: " or "Week: " to the xAxis fields. Then simply merged the results from both the Daily and Weekly branches and made this the output of the Mashup.

Hopefully this will get you a few steps further down the process.

Thank you very much Sara , This helped a lot.

I am done with having Day and Week filters now , my next step would be to work on some other additional requirements of the App ,

I really appreciate the detailed explanation for the beginners like me,

This is a very helpful forum.Thank you!!

Everything looks good except one small issue which i am unable to figure out , i have added month and quarter in the similar fashion but when ever i select the any other filter other than quarter i get quarter record shown , but it does not have any data , i have deleted the transfomer,data decorator and tried but still this exits , could you please see why , i am sure it must be something minor which i am not able find :-(

Here is the screencast....

http://screencast.com/t/QOltfBgvvSUP

Success! or almost :-)

Just to be clear, when you choose Week as the query type, for example, and you run the Merge block, the results show records for each week with data in all the columns. But it also shows records for each quarter? or at least there is a record with a label in the xAxis field -- but no data for any of the other columns?

I'm having the same problem following the screencast (the size is so big I can't see what block you're running while also looking at the results -- makes it hard to follow exactly what is happening). Can you open the mashup in Wires, click View EMML, copy the code and send it to me in email (sara.mitchell@jackbe.com)?

Yes , thats right , i get an extra record with no data, attached the screenshot of the data when week is the queryinput

I did find an error in the EMML which is not a user error. So far I haven't been able to reproduce this in my environment, but I will report the problem.

Try deleting the Transformer and Data Decorator blocks for the Quarter conditional branch. Be sure to save the mashup at this point.

Then add the Transformer block and Data Decorator block for Quarter back into the Quarter conditional branch. Run Data Decorator when the query type is Week or Day. You should not get any data, even a blank record. Hopefully, this will fix the problem.

We took this offline, but seem to have finally gotten this working. So for other users who may be interested, the solution was primarily to close Wires (and perhaps close the browser altogether), clear cookies and cache.