Some Guidance please

[JeffGrant]

I have a java API that downloads and combines csv files from a data site, ready for import into excel.
Basically, the API gets a particular file, then loops through all of the lines in the file to pull down the relevant data for the day.

The data suppler also supplies the data as json files and they are making noises about stopping the supply of the csv files.

With PQ, i can see there are many videos about pulling a single data file with a known URL, but i have not seen anything that would effectively loop through an input list to create the URL as an input variable.

Is this possible?

I am a real novice with PQ, and a little above novice with VBA, so I can grab the jist of it without claiming be a professional. so I do understand the technical parts of what i am asking here.

I look forward to hearing from your guys soon.

 

[JeffGrant]

oops sorry, today there were 4 tracks, with 7,7,8 & 8 races respectively.

{"IsError":false,"IsOverRequestLimit":false,"PageDescription":"","PageTitle":"","Result":[{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686534027000+1000)\/","MeetingId":189692,"RaceCount":7,"RaceNumbers":[1,2,3,4,5,6,7],"Resulted":true,"State":"NSW","TABMeeting":true,"Track":"Ballina","TrackId":108},{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686535137000+1000)\/","MeetingId":189693,"RaceCount":7,"RaceNumbers":[1,2,3,4,5,6,7],"Resulted":true,"State":"QLD","TABMeeting":true,"Track":"Cairns","TrackId":153},{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686533127000+1000)\/","MeetingId":189698,"RaceCount":8,"RaceNumbers":[1,2,3,4,5,6,7,8],"Resulted":true,"State":"VIC","TABMeeting":true,"Track":"Mornington","TrackId":315},{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686535561000+1000)\/","MeetingId":189694,"RaceCount":8,"RaceNumbers":[1,2,3,4,5,6,7,8],"Resulted":true,"State":"SA","TABMeeting":true,"Track":"Murray Bridge GH","TrackId":1151},{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686533574000+1000)\/","MeetingId":189672,"RaceCount":8,"RaceNumbers":[1,2,3,4,5,6,7,8],"Resulted":true,"State":"NSW","TABMeeting":true,"Track":"Warwick Farm","TrackId":126}],"ServerTime":null}

 

[JeffGrant]

I must say though, there is a lot less code in M than there is in JAVA (see I am slowly gettgn thehang of it )

 

[smozgur]

oops sorry, today there were 4 tracks, with 7,7,8 & 8 races respectively.

{"IsError":false,"IsOverRequestLimit":false,"PageDescription":"","PageTitle":"","Result":[{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686534027000+1000)\/","MeetingId":189692,"RaceCount":7,"RaceNumbers":[1,2,3,4,5,6,7],"Resulted":true,"State":"NSW","TABMeeting":true,"Track":"Ballina","TrackId":108},{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686535137000+1000)\/","MeetingId":189693,"RaceCount":7,"RaceNumbers":[1,2,3,4,5,6,7],"Resulted":true,"State":"QLD","TABMeeting":true,"Track":"Cairns","TrackId":153},{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686533127000+1000)\/","MeetingId":189698,"RaceCount":8,"RaceNumbers":[1,2,3,4,5,6,7,8],"Resulted":true,"State":"VIC","TABMeeting":true,"Track":"Mornington","TrackId":315},{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686535561000+1000)\/","MeetingId":189694,"RaceCount":8,"RaceNumbers":[1,2,3,4,5,6,7,8],"Resulted":true,"State":"SA","TABMeeting":true,"Track":"Murray Bridge GH","TrackId":1151},{"Date":"\/Date(1686492000000+1000)\/","DateStamp":"\/Date(1686533574000+1000)\/","MeetingId":189672,"RaceCount":8,"RaceNumbers":[1,2,3,4,5,6,7,8],"Resulted":true,"State":"NSW","TABMeeting":true,"Track":"Warwick Farm","TrackId":126}],"ServerTime":null}

Are you sure that you are using today as 13-Jun-2023?

 

[smozgur]

thanks. all i get back is the first track and race one only. is that what you are expecting?
Today there are 3 tracks with 12, 7 & 7 races respectively.

Not really. It returns one row for each race for each track. Something like below.

 

[JeffGrant]

Jolly good point. That would help for sure....

when I use the right date I get this as the API call results....

{"IsError":false,"IsOverRequestLimit":false,"PageDescription":"","PageTitle":"","Result":[{"Date":"\/Date(1686578400000+1000)\/","DateStamp":"\/Date(1686619062000+1000)\/","MeetingId":189699,"RaceCount":9,"RaceNumbers":[1,2,3,4,5,6,7,8,9],"Resulted":true,"State":"VIC","TABMeeting":true,"Track":"Ballarat Synthetic","TrackId":1150},{"Date":"\/Date(1686578400000+1000)\/","DateStamp":"\/Date(1686619730000+1000)\/","MeetingId":189697,"RaceCount":7,"RaceNumbers":[1,2,3,4,5,6,7],"Resulted":true,"State":"NSW","TABMeeting":true,"Track":"Scone","TrackId":105},{"Date":"\/Date(1686578400000+1000)\/","DateStamp":"\/Date(1686620133000+1000)\/","MeetingId":189696,"RaceCount":7,"RaceNumbers":[1,2,3,4,5,6,7],"Resulted":true,"State":"NSW","TABMeeting":true,"Track":"Wagga","TrackId":509}],"ServerTime":null}

when i use the query, all i am getting Ballarat Synthetic and Race Number1

 

[JeffGrant]

I am not seeing that list as you are for some reason. I am seeing the next step. The expanded record

 

[JeffGrant]

I can see the image you created, with columns moved, at step TableFromCSV. it looks like it is the next two steps wher it changes

 

[smozgur]

When you first run the query, did you get a credentials message and accept to allow connection to the server?

When you click on the last step, TableFromCSV, what do you see in the preview section?

Perhaps, did you possibly click on the Table in the first row that extracts that table only and now seeing more steps in your applied steps?

 

[JeffGrant]

Let me do it again

 

[JeffGrant]

I created a new qury, pasted the code, and got the permissions. no drama.
upon reordering the columns to match your image, i get the same data at Table FromCSV Step.

Looking your original code, I must have expanded the table record. Oops sorry, the GUI acts like a step recorder and I must have pressed the extra steps