Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Data Works for AI is here - Join the discussion and enter to win a pair of Qlik kicks: Join the Conversation!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ryan101
Contributor
Contributor
Wednesday

Need help with Data Load script fetching data using Rest Connector.

I am using Rest Connector to fetch data from S&P 500 to get the data for a specific ticker.

Below is my auto-generated script -

RestConnectorMasterTable:

SQL SELECT 
"__KEY_root",
(SELECT 
"__KEY_Time Series (Daily)",
"__FK_Time Series (Daily)",
(SELECT 
"1. open",
"2. high",
"3. low",
"4. close",
"5. volume",
"__FK_2026-07-24"
FROM "2026-07-24" FK "__FK_2026-07-24"),
(SELECT 
"1. open" AS "1. open_u0",
"2. high" AS "2. high_u0",
"3. low" AS "3. low_u0",
"4. close" AS "4. close_u0",
"5. volume" AS "5. volume_u0",
"__FK_2026-07-23"
FROM "2026-07-23" FK "__FK_2026-07-23"),
(SELECT 
"1. open" AS "1. open_u1",
"2. high" AS "2. high_u1",
"3. low" AS "3. low_u1",
"4. close" AS "4. close_u1",
"5. volume" AS "5. volume_u1",
"__FK_2026-07-22"
FROM "2026-07-22" FK "__FK_2026-07-22"),
(SELECT 
"1. open" AS "1. open_u2",
"2. high" AS "2. high_u2",
"3. low" AS "3. low_u2",
"4. close" AS "4. close_u2",
"5. volume" AS "5. volume_u2",
"__FK_2026-07-21"
FROM "2026-07-21" FK "__FK_2026-07-21"),
(SELECT 
"1. open" AS "1. open_u3",
"2. high" AS "2. high_u3",
"3. low" AS "3. low_u3",
"4. close" AS "4. close_u3",
"5. volume" AS "5. volume_u3",
"__FK_2026-07-20"
FROM "2026-07-20" FK "__FK_2026-07-20"),
(SELECT 
"1. open" AS "1. open_u4",
"2. high" AS "2. high_u4",
"3. low" AS "3. low_u4",
"4. close" AS "4. close_u4",
"5. volume" AS "5. volume_u4",
"__FK_2026-07-17"
FROM "2026-07-17" FK "__FK_2026-07-17"),
(SELECT 
"1. open" AS "1. open_u5",
"2. high" AS "2. high_u5",
"3. low" AS "3. low_u5",
"4. close" AS "4. close_u5",
"5. volume" AS "5. volume_u5",
"__FK_2026-07-16"
FROM "2026-07-16" FK "__FK_2026-07-16"),
(SELECT 
"1. open" AS "1. open_u6",
"2. high" AS "2. high_u6",
"3. low" AS "3. low_u6",
"4. close" AS "4. close_u6",
"5. volume" AS "5. volume_u6",
"__FK_2026-07-15"
FROM "2026-07-15" FK "__FK_2026-07-15"),
(SELECT 
"1. open" AS "1. open_u7",
"2. high" AS "2. high_u7",
"3. low" AS "3. low_u7",
"4. close" AS "4. close_u7",
"5. volume" AS "5. volume_u7",
"__FK_2026-07-14"
FROM "2026-07-14" FK "__FK_2026-07-14"),
(SELECT 
"1. open" AS "1. open_u8",
"2. high" AS "2. high_u8",
"3. low" AS "3. low_u8",
"4. close" AS "4. close_u8",
"5. volume" AS "5. volume_u8",
"__FK_2026-07-13"
FROM "2026-07-13" FK "__FK_2026-07-13"),
(SELECT 
"1. open" AS "1. open_u9",
"2. high" AS "2. high_u9",
"3. low" AS "3. low_u9",
"4. close" AS "4. close_u9",
"5. volume" AS "5. volume_u9",
"__FK_2026-07-10"
FROM "2026-07-10" FK "__FK_2026-07-10"),
(SELECT 
"1. open" AS "1. open_u10",
"2. high" AS "2. high_u10",
"3. low" AS "3. low_u10",
"4. close" AS "4. close_u10",
"5. volume" AS "5. volume_u10",
"__FK_2026-07-09"
FROM "2026-07-09" FK "__FK_2026-07-09"),
(SELECT 
"1. open" AS "1. open_u11",
"2. high" AS "2. high_u11",
"3. low" AS "3. low_u11",
"4. close" AS "4. close_u11",
"5. volume" AS "5. volume_u11",
"__FK_2026-07-08"
FROM "2026-07-08" FK "__FK_2026-07-08"),
(SELECT 
"1. open" AS "1. open_u12",
"2. high" AS "2. high_u12",
"3. low" AS "3. low_u12",
"4. close" AS "4. close_u12",
"5. volume" AS "5. volume_u12",
"__FK_2026-07-07"
FROM "2026-07-07" FK "__FK_2026-07-07"),
(SELECT 
"1. open" AS "1. open_u13",
"2. high" AS "2. high_u13",
"3. low" AS "3. low_u13",
"4. close" AS "4. close_u13",
"5. volume" AS "5. volume_u13",
"__FK_2026-07-06"
FROM "2026-07-06" FK "__FK_2026-07-06"),
(SELECT 
"1. open" AS "1. open_u14",
"2. high" AS "2. high_u14",
"3. low" AS "3. low_u14",
"4. close" AS "4. close_u14",
"5. volume" AS "5. volume_u14",
"__FK_2026-07-02"
FROM "2026-07-02" FK "__FK_2026-07-02"),
(SELECT 
"1. open" AS "1. open_u15",
"2. high" AS "2. high_u15",
"3. low" AS "3. low_u15",
"4. close" AS "4. close_u15",
"5. volume" AS "5. volume_u15",
"__FK_2026-07-01"
FROM "2026-07-01" FK "__FK_2026-07-01"),
(SELECT 
"1. open" AS "1. open_u16",
"2. high" AS "2. high_u16",
"3. low" AS "3. low_u16",
"4. close" AS "4. close_u16",
"5. volume" AS "5. volume_u16",
"__FK_2026-06-30"
FROM "2026-06-30" FK "__FK_2026-06-30"),
(SELECT 
"1. open" AS "1. open_u17",
"2. high" AS "2. high_u17",
"3. low" AS "3. low_u17",
"4. close" AS "4. close_u17",
"5. volume" AS "5. volume_u17",
"__FK_2026-06-29"
FROM "2026-06-29" FK "__FK_2026-06-29"),
(SELECT 
"1. open" AS "1. open_u18",
"2. high" AS "2. high_u18",
"3. low" AS "3. low_u18",
"4. close" AS "4. close_u18",
"5. volume" AS "5. volume_u18",
"__FK_2026-06-26"
FROM "2026-06-26" FK "__FK_2026-06-26"),
(SELECT 
"1. open" AS "1. open_u19",
"2. high" AS "2. high_u19",
"3. low" AS "3. low_u19",
"4. close" AS "4. close_u19",
"5. volume" AS "5. volume_u19",
"__FK_2026-06-25"
FROM "2026-06-25" FK "__FK_2026-06-25"),
(SELECT 
"1. open" AS "1. open_u20",
"2. high" AS "2. high_u20",
"3. low" AS "3. low_u20",
"4. close" AS "4. close_u20",
"5. volume" AS "5. volume_u20",
"__FK_2026-06-24"
FROM "2026-06-24" FK "__FK_2026-06-24"),
(SELECT 
"1. open" AS "1. open_u21",
"2. high" AS "2. high_u21",
"3. low" AS "3. low_u21",
"4. close" AS "4. close_u21",
"5. volume" AS "5. volume_u21",
"__FK_2026-06-23"
FROM "2026-06-23" FK "__FK_2026-06-23"),
(SELECT 
"1. open" AS "1. open_u22",
"2. high" AS "2. high_u22",
"3. low" AS "3. low_u22",
"4. close" AS "4. close_u22",
"5. volume" AS "5. volume_u22",
"__FK_2026-06-22"
FROM "2026-06-22" FK "__FK_2026-06-22"),
(SELECT 
"1. open" AS "1. open_u23",
"2. high" AS "2. high_u23",
"3. low" AS "3. low_u23",
"4. close" AS "4. close_u23",
"5. volume" AS "5. volume_u23",
"__FK_2026-06-18"
FROM "2026-06-18" FK "__FK_2026-06-18"),
(SELECT 
"1. open" AS "1. open_u24",
"2. high" AS "2. high_u24",
"3. low" AS "3. low_u24",
"4. close" AS "4. close_u24",
"5. volume" AS "5. volume_u24",
"__FK_2026-06-17"
FROM "2026-06-17" FK "__FK_2026-06-17"),
(SELECT 
"1. open" AS "1. open_u25",
"2. high" AS "2. high_u25",
"3. low" AS "3. low_u25",
"4. close" AS "4. close_u25",
"5. volume" AS "5. volume_u25",
"__FK_2026-06-16"
FROM "2026-06-16" FK "__FK_2026-06-16"),
(SELECT 
"1. open" AS "1. open_u26",
"2. high" AS "2. high_u26",
"3. low" AS "3. low_u26",
"4. close" AS "4. close_u26",
"5. volume" AS "5. volume_u26",
"__FK_2026-06-15"
FROM "2026-06-15" FK "__FK_2026-06-15"),
(SELECT 
"1. open" AS "1. open_u27",
"2. high" AS "2. high_u27",
"3. low" AS "3. low_u27",
"4. close" AS "4. close_u27",
"5. volume" AS "5. volume_u27",
"__FK_2026-06-12"
FROM "2026-06-12" FK "__FK_2026-06-12"),
(SELECT 
"1. open" AS "1. open_u28",
"2. high" AS "2. high_u28",
"3. low" AS "3. low_u28",
"4. close" AS "4. close_u28",
"5. volume" AS "5. volume_u28",
"__FK_2026-06-11"
FROM "2026-06-11" FK "__FK_2026-06-11"),
(SELECT 
"1. open" AS "1. open_u29",
"2. high" AS "2. high_u29",
"3. low" AS "3. low_u29",
"4. close" AS "4. close_u29",
"5. volume" AS "5. volume_u29",
"__FK_2026-06-10"
FROM "2026-06-10" FK "__FK_2026-06-10"),
(SELECT 
"1. open" AS "1. open_u30",
"2. high" AS "2. high_u30",
"3. low" AS "3. low_u30",
"4. close" AS "4. close_u30",
"5. volume" AS "5. volume_u30",
"__FK_2026-06-09"
FROM "2026-06-09" FK "__FK_2026-06-09"),
(SELECT 
"1. open" AS "1. open_u31",
"2. high" AS "2. high_u31",
"3. low" AS "3. low_u31",
"4. close" AS "4. close_u31",
"5. volume" AS "5. volume_u31",
"__FK_2026-06-08"
FROM "2026-06-08" FK "__FK_2026-06-08"),
(SELECT 
"1. open" AS "1. open_u32",
"2. high" AS "2. high_u32",
"3. low" AS "3. low_u32",
"4. close" AS "4. close_u32",
"5. volume" AS "5. volume_u32",
"__FK_2026-06-05"
FROM "2026-06-05" FK "__FK_2026-06-05"),
(SELECT 
"1. open" AS "1. open_u33",
"2. high" AS "2. high_u33",
"3. low" AS "3. low_u33",
"4. close" AS "4. close_u33",
"5. volume" AS "5. volume_u33",
"__FK_2026-06-04"
FROM "2026-06-04" FK "__FK_2026-06-04"),
(SELECT 
"1. open" AS "1. open_u34",
"2. high" AS "2. high_u34",
"3. low" AS "3. low_u34",
"4. close" AS "4. close_u34",
"5. volume" AS "5. volume_u34",
"__FK_2026-06-03"
FROM "2026-06-03" FK "__FK_2026-06-03"),
(SELECT 
"1. open" AS "1. open_u35",
"2. high" AS "2. high_u35",
"3. low" AS "3. low_u35",
"4. close" AS "4. close_u35",
"5. volume" AS "5. volume_u35",
"__FK_2026-06-02"
FROM "2026-06-02" FK "__FK_2026-06-02"),
(SELECT 
"1. open" AS "1. open_u36",
"2. high" AS "2. high_u36",
"3. low" AS "3. low_u36",
"4. close" AS "4. close_u36",
"5. volume" AS "5. volume_u36",
"__FK_2026-06-01"
FROM "2026-06-01" FK "__FK_2026-06-01"),
(SELECT 
"1. open" AS "1. open_u37",
"2. high" AS "2. high_u37",
"3. low" AS "3. low_u37",
"4. close" AS "4. close_u37",
"5. volume" AS "5. volume_u37",
"__FK_2026-05-29"
FROM "2026-05-29" FK "__FK_2026-05-29"),
(SELECT 
"1. open" AS "1. open_u38",
"2. high" AS "2. high_u38",
"3. low" AS "3. low_u38",
"4. close" AS "4. close_u38",
"5. volume" AS "5. volume_u38",
"__FK_2026-05-28"
FROM "2026-05-28" FK "__FK_2026-05-28"),
(SELECT 
"1. open" AS "1. open_u39",
"2. high" AS "2. high_u39",
"3. low" AS "3. low_u39",
"4. close" AS "4. close_u39",
"5. volume" AS "5. volume_u39",
"__FK_2026-05-27"
FROM "2026-05-27" FK "__FK_2026-05-27"),
(SELECT 
"1. open" AS "1. open_u40",
"2. high" AS "2. high_u40",
"3. low" AS "3. low_u40",
"4. close" AS "4. close_u40",
"5. volume" AS "5. volume_u40",
"__FK_2026-05-26"
FROM "2026-05-26" FK "__FK_2026-05-26"),
(SELECT 
"1. open" AS "1. open_u41",
"2. high" AS "2. high_u41",
"3. low" AS "3. low_u41",
"4. close" AS "4. close_u41",
"5. volume" AS "5. volume_u41",
"__FK_2026-05-22"
FROM "2026-05-22" FK "__FK_2026-05-22"),
(SELECT 
"1. open" AS "1. open_u42",
"2. high" AS "2. high_u42",
"3. low" AS "3. low_u42",
"4. close" AS "4. close_u42",
"5. volume" AS "5. volume_u42",
"__FK_2026-05-21"
FROM "2026-05-21" FK "__FK_2026-05-21"),
(SELECT 
"1. open" AS "1. open_u43",
"2. high" AS "2. high_u43",
"3. low" AS "3. low_u43",
"4. close" AS "4. close_u43",
"5. volume" AS "5. volume_u43",
"__FK_2026-05-20"
FROM "2026-05-20" FK "__FK_2026-05-20"),
(SELECT 
"1. open" AS "1. open_u44",
"2. high" AS "2. high_u44",
"3. low" AS "3. low_u44",
"4. close" AS "4. close_u44",
"5. volume" AS "5. volume_u44",
"__FK_2026-05-19"
FROM "2026-05-19" FK "__FK_2026-05-19"),
(SELECT 
"1. open" AS "1. open_u45",
"2. high" AS "2. high_u45",
"3. low" AS "3. low_u45",
"4. close" AS "4. close_u45",
"5. volume" AS "5. volume_u45",
"__FK_2026-05-18"
FROM "2026-05-18" FK "__FK_2026-05-18"),
(SELECT 
"1. open" AS "1. open_u46",
"2. high" AS "2. high_u46",
"3. low" AS "3. low_u46",
"4. close" AS "4. close_u46",
"5. volume" AS "5. volume_u46",
"__FK_2026-05-15"
FROM "2026-05-15" FK "__FK_2026-05-15"),
(SELECT 
"1. open" AS "1. open_u47",
"2. high" AS "2. high_u47",
"3. low" AS "3. low_u47",
"4. close" AS "4. close_u47",
"5. volume" AS "5. volume_u47",
"__FK_2026-05-14"
FROM "2026-05-14" FK "__FK_2026-05-14"),
(SELECT 
"1. open" AS "1. open_u48",
"2. high" AS "2. high_u48",
"3. low" AS "3. low_u48",
"4. close" AS "4. close_u48",
"5. volume" AS "5. volume_u48",
"__FK_2026-05-13"
FROM "2026-05-13" FK "__FK_2026-05-13"),
(SELECT 
"1. open" AS "1. open_u49",
"2. high" AS "2. high_u49",
"3. low" AS "3. low_u49",
"4. close" AS "4. close_u49",
"5. volume" AS "5. volume_u49",
"__FK_2026-05-12"
FROM "2026-05-12" FK "__FK_2026-05-12"),
(SELECT 
"1. open" AS "1. open_u50",
"2. high" AS "2. high_u50",
"3. low" AS "3. low_u50",
"4. close" AS "4. close_u50",
"5. volume" AS "5. volume_u50",
"__FK_2026-05-11"
FROM "2026-05-11" FK "__FK_2026-05-11"),
(SELECT 
"1. open" AS "1. open_u51",
"2. high" AS "2. high_u51",
"3. low" AS "3. low_u51",
"4. close" AS "4. close_u51",
"5. volume" AS "5. volume_u51",
"__FK_2026-05-08"
FROM "2026-05-08" FK "__FK_2026-05-08"),
(SELECT 
"1. open" AS "1. open_u52",
"2. high" AS "2. high_u52",
"3. low" AS "3. low_u52",
"4. close" AS "4. close_u52",
"5. volume" AS "5. volume_u52",
"__FK_2026-05-07"
FROM "2026-05-07" FK "__FK_2026-05-07"),
(SELECT 
"1. open" AS "1. open_u53",
"2. high" AS "2. high_u53",
"3. low" AS "3. low_u53",
"4. close" AS "4. close_u53",
"5. volume" AS "5. volume_u53",
"__FK_2026-05-06"
FROM "2026-05-06" FK "__FK_2026-05-06"),
(SELECT 
"1. open" AS "1. open_u54",
"2. high" AS "2. high_u54",
"3. low" AS "3. low_u54",
"4. close" AS "4. close_u54",
"5. volume" AS "5. volume_u54",
"__FK_2026-05-05"
FROM "2026-05-05" FK "__FK_2026-05-05"),
(SELECT 
"1. open" AS "1. open_u55",
"2. high" AS "2. high_u55",
"3. low" AS "3. low_u55",
"4. close" AS "4. close_u55",
"5. volume" AS "5. volume_u55",
"__FK_2026-05-04"
FROM "2026-05-04" FK "__FK_2026-05-04"),
(SELECT 
"1. open" AS "1. open_u56",
"2. high" AS "2. high_u56",
"3. low" AS "3. low_u56",
"4. close" AS "4. close_u56",
"5. volume" AS "5. volume_u56",
"__FK_2026-05-01"
FROM "2026-05-01" FK "__FK_2026-05-01"),
(SELECT 
"1. open" AS "1. open_u57",
"2. high" AS "2. high_u57",
"3. low" AS "3. low_u57",
"4. close" AS "4. close_u57",
"5. volume" AS "5. volume_u57",
"__FK_2026-04-30"
FROM "2026-04-30" FK "__FK_2026-04-30"),
(SELECT 
"1. open" AS "1. open_u58",
"2. high" AS "2. high_u58",
"3. low" AS "3. low_u58",
"4. close" AS "4. close_u58",
"5. volume" AS "5. volume_u58",
"__FK_2026-04-29"
FROM "2026-04-29" FK "__FK_2026-04-29"),
(SELECT 
"1. open" AS "1. open_u59",
"2. high" AS "2. high_u59",
"3. low" AS "3. low_u59",
"4. close" AS "4. close_u59",
"5. volume" AS "5. volume_u59",
"__FK_2026-04-28"
FROM "2026-04-28" FK "__FK_2026-04-28"),
(SELECT 
"1. open" AS "1. open_u60",
"2. high" AS "2. high_u60",
"3. low" AS "3. low_u60",
"4. close" AS "4. close_u60",
"5. volume" AS "5. volume_u60",
"__FK_2026-04-27"
FROM "2026-04-27" FK "__FK_2026-04-27"),
(SELECT 
"1. open" AS "1. open_u61",
"2. high" AS "2. high_u61",
"3. low" AS "3. low_u61",
"4. close" AS "4. close_u61",
"5. volume" AS "5. volume_u61",
"__FK_2026-04-24"
FROM "2026-04-24" FK "__FK_2026-04-24"),
(SELECT 
"1. open" AS "1. open_u62",
"2. high" AS "2. high_u62",
"3. low" AS "3. low_u62",
"4. close" AS "4. close_u62",
"5. volume" AS "5. volume_u62",
"__FK_2026-04-23"
FROM "2026-04-23" FK "__FK_2026-04-23"),
(SELECT 
"1. open" AS "1. open_u63",
"2. high" AS "2. high_u63",
"3. low" AS "3. low_u63",
"4. close" AS "4. close_u63",
"5. volume" AS "5. volume_u63",
"__FK_2026-04-22"
FROM "2026-04-22" FK "__FK_2026-04-22"),
(SELECT 
"1. open" AS "1. open_u64",
"2. high" AS "2. high_u64",
"3. low" AS "3. low_u64",
"4. close" AS "4. close_u64",
"5. volume" AS "5. volume_u64",
"__FK_2026-04-21"
FROM "2026-04-21" FK "__FK_2026-04-21"),
(SELECT 
"1. open" AS "1. open_u65",
"2. high" AS "2. high_u65",
"3. low" AS "3. low_u65",
"4. close" AS "4. close_u65",
"5. volume" AS "5. volume_u65",
"__FK_2026-04-20"
FROM "2026-04-20" FK "__FK_2026-04-20"),
(SELECT 
"1. open" AS "1. open_u66",
"2. high" AS "2. high_u66",
"3. low" AS "3. low_u66",
"4. close" AS "4. close_u66",
"5. volume" AS "5. volume_u66",
"__FK_2026-04-17"
FROM "2026-04-17" FK "__FK_2026-04-17"),
(SELECT 
"1. open" AS "1. open_u67",
"2. high" AS "2. high_u67",
"3. low" AS "3. low_u67",
"4. close" AS "4. close_u67",
"5. volume" AS "5. volume_u67",
"__FK_2026-04-16"
FROM "2026-04-16" FK "__FK_2026-04-16"),
(SELECT 
"1. open" AS "1. open_u68",
"2. high" AS "2. high_u68",
"3. low" AS "3. low_u68",
"4. close" AS "4. close_u68",
"5. volume" AS "5. volume_u68",
"__FK_2026-04-15"
FROM "2026-04-15" FK "__FK_2026-04-15"),
(SELECT 
"1. open" AS "1. open_u69",
"2. high" AS "2. high_u69",
"3. low" AS "3. low_u69",
"4. close" AS "4. close_u69",
"5. volume" AS "5. volume_u69",
"__FK_2026-04-14"
FROM "2026-04-14" FK "__FK_2026-04-14"),
(SELECT 
"1. open" AS "1. open_u70",
"2. high" AS "2. high_u70",
"3. low" AS "3. low_u70",
"4. close" AS "4. close_u70",
"5. volume" AS "5. volume_u70",
"__FK_2026-04-13"
FROM "2026-04-13" FK "__FK_2026-04-13"),
(SELECT 
"1. open" AS "1. open_u71",
"2. high" AS "2. high_u71",
"3. low" AS "3. low_u71",
"4. close" AS "4. close_u71",
"5. volume" AS "5. volume_u71",
"__FK_2026-04-10"
FROM "2026-04-10" FK "__FK_2026-04-10"),
(SELECT 
"1. open" AS "1. open_u72",
"2. high" AS "2. high_u72",
"3. low" AS "3. low_u72",
"4. close" AS "4. close_u72",
"5. volume" AS "5. volume_u72",
"__FK_2026-04-09"
FROM "2026-04-09" FK "__FK_2026-04-09"),
(SELECT 
"1. open" AS "1. open_u73",
"2. high" AS "2. high_u73",
"3. low" AS "3. low_u73",
"4. close" AS "4. close_u73",
"5. volume" AS "5. volume_u73",
"__FK_2026-04-08"
FROM "2026-04-08" FK "__FK_2026-04-08"),
(SELECT 
"1. open" AS "1. open_u74",
"2. high" AS "2. high_u74",
"3. low" AS "3. low_u74",
"4. close" AS "4. close_u74",
"5. volume" AS "5. volume_u74",
"__FK_2026-04-07"
FROM "2026-04-07" FK "__FK_2026-04-07"),
(SELECT 
"1. open" AS "1. open_u75",
"2. high" AS "2. high_u75",
"3. low" AS "3. low_u75",
"4. close" AS "4. close_u75",
"5. volume" AS "5. volume_u75",
"__FK_2026-04-06"
FROM "2026-04-06" FK "__FK_2026-04-06"),
(SELECT 
"1. open" AS "1. open_u76",
"2. high" AS "2. high_u76",
"3. low" AS "3. low_u76",
"4. close" AS "4. close_u76",
"5. volume" AS "5. volume_u76",
"__FK_2026-04-02"
FROM "2026-04-02" FK "__FK_2026-04-02"),
(SELECT 
"1. open" AS "1. open_u77",
"2. high" AS "2. high_u77",
"3. low" AS "3. low_u77",
"4. close" AS "4. close_u77",
"5. volume" AS "5. volume_u77",
"__FK_2026-04-01"
FROM "2026-04-01" FK "__FK_2026-04-01"),
(SELECT 
"1. open" AS "1. open_u78",
"2. high" AS "2. high_u78",
"3. low" AS "3. low_u78",
"4. close" AS "4. close_u78",
"5. volume" AS "5. volume_u78",
"__FK_2026-03-31"
FROM "2026-03-31" FK "__FK_2026-03-31"),
(SELECT 
"1. open" AS "1. open_u79",
"2. high" AS "2. high_u79",
"3. low" AS "3. low_u79",
"4. close" AS "4. close_u79",
"5. volume" AS "5. volume_u79",
"__FK_2026-03-30"
FROM "2026-03-30" FK "__FK_2026-03-30"),
(SELECT 
"1. open" AS "1. open_u80",
"2. high" AS "2. high_u80",
"3. low" AS "3. low_u80",
"4. close" AS "4. close_u80",
"5. volume" AS "5. volume_u80",
"__FK_2026-03-27"
FROM "2026-03-27" FK "__FK_2026-03-27"),
(SELECT 
"1. open" AS "1. open_u81",
"2. high" AS "2. high_u81",
"3. low" AS "3. low_u81",
"4. close" AS "4. close_u81",
"5. volume" AS "5. volume_u81",
"__FK_2026-03-26"
FROM "2026-03-26" FK "__FK_2026-03-26"),
(SELECT 
"1. open" AS "1. open_u82",
"2. high" AS "2. high_u82",
"3. low" AS "3. low_u82",
"4. close" AS "4. close_u82",
"5. volume" AS "5. volume_u82",
"__FK_2026-03-25"
FROM "2026-03-25" FK "__FK_2026-03-25"),
(SELECT 
"1. open" AS "1. open_u83",
"2. high" AS "2. high_u83",
"3. low" AS "3. low_u83",
"4. close" AS "4. close_u83",
"5. volume" AS "5. volume_u83",
"__FK_2026-03-24"
FROM "2026-03-24" FK "__FK_2026-03-24"),
(SELECT 
"1. open" AS "1. open_u84",
"2. high" AS "2. high_u84",
"3. low" AS "3. low_u84",
"4. close" AS "4. close_u84",
"5. volume" AS "5. volume_u84",
"__FK_2026-03-23"
FROM "2026-03-23" FK "__FK_2026-03-23"),
(SELECT 
"1. open" AS "1. open_u85",
"2. high" AS "2. high_u85",
"3. low" AS "3. low_u85",
"4. close" AS "4. close_u85",
"5. volume" AS "5. volume_u85",
"__FK_2026-03-20"
FROM "2026-03-20" FK "__FK_2026-03-20"),
(SELECT 
"1. open" AS "1. open_u86",
"2. high" AS "2. high_u86",
"3. low" AS "3. low_u86",
"4. close" AS "4. close_u86",
"5. volume" AS "5. volume_u86",
"__FK_2026-03-19"
FROM "2026-03-19" FK "__FK_2026-03-19"),
(SELECT 
"1. open" AS "1. open_u87",
"2. high" AS "2. high_u87",
"3. low" AS "3. low_u87",
"4. close" AS "4. close_u87",
"5. volume" AS "5. volume_u87",
"__FK_2026-03-18"
FROM "2026-03-18" FK "__FK_2026-03-18"),
(SELECT 
"1. open" AS "1. open_u88",
"2. high" AS "2. high_u88",
"3. low" AS "3. low_u88",
"4. close" AS "4. close_u88",
"5. volume" AS "5. volume_u88",
"__FK_2026-03-17"
FROM "2026-03-17" FK "__FK_2026-03-17"),
(SELECT 
"1. open" AS "1. open_u89",
"2. high" AS "2. high_u89",
"3. low" AS "3. low_u89",
"4. close" AS "4. close_u89",
"5. volume" AS "5. volume_u89",
"__FK_2026-03-16"
FROM "2026-03-16" FK "__FK_2026-03-16"),
(SELECT 
"1. open" AS "1. open_u90",
"2. high" AS "2. high_u90",
"3. low" AS "3. low_u90",
"4. close" AS "4. close_u90",
"5. volume" AS "5. volume_u90",
"__FK_2026-03-13"
FROM "2026-03-13" FK "__FK_2026-03-13"),
(SELECT 
"1. open" AS "1. open_u91",
"2. high" AS "2. high_u91",
"3. low" AS "3. low_u91",
"4. close" AS "4. close_u91",
"5. volume" AS "5. volume_u91",
"__FK_2026-03-12"
FROM "2026-03-12" FK "__FK_2026-03-12"),
(SELECT 
"1. open" AS "1. open_u92",
"2. high" AS "2. high_u92",
"3. low" AS "3. low_u92",
"4. close" AS "4. close_u92",
"5. volume" AS "5. volume_u92",
"__FK_2026-03-11"
FROM "2026-03-11" FK "__FK_2026-03-11"),
(SELECT 
"1. open" AS "1. open_u93",
"2. high" AS "2. high_u93",
"3. low" AS "3. low_u93",
"4. close" AS "4. close_u93",
"5. volume" AS "5. volume_u93",
"__FK_2026-03-10"
FROM "2026-03-10" FK "__FK_2026-03-10"),
(SELECT 
"1. open" AS "1. open_u94",
"2. high" AS "2. high_u94",
"3. low" AS "3. low_u94",
"4. close" AS "4. close_u94",
"5. volume" AS "5. volume_u94",
"__FK_2026-03-09"
FROM "2026-03-09" FK "__FK_2026-03-09"),
(SELECT 
"1. open" AS "1. open_u95",
"2. high" AS "2. high_u95",
"3. low" AS "3. low_u95",
"4. close" AS "4. close_u95",
"5. volume" AS "5. volume_u95",
"__FK_2026-03-06"
FROM "2026-03-06" FK "__FK_2026-03-06"),
(SELECT 
"1. open" AS "1. open_u96",
"2. high" AS "2. high_u96",
"3. low" AS "3. low_u96",
"4. close" AS "4. close_u96",
"5. volume" AS "5. volume_u96",
"__FK_2026-03-05"
FROM "2026-03-05" FK "__FK_2026-03-05"),
(SELECT 
"1. open" AS "1. open_u97",
"2. high" AS "2. high_u97",
"3. low" AS "3. low_u97",
"4. close" AS "4. close_u97",
"5. volume" AS "5. volume_u97",
"__FK_2026-03-04"
FROM "2026-03-04" FK "__FK_2026-03-04"),
(SELECT 
"1. open" AS "1. open_u98",
"2. high" AS "2. high_u98",
"3. low" AS "3. low_u98",
"4. close" AS "4. close_u98",
"5. volume" AS "5. volume_u98",
"__FK_2026-03-03"
FROM "2026-03-03" FK "__FK_2026-03-03")
FROM "Time Series (Daily)" PK "__KEY_Time Series (Daily)" FK "__FK_Time Series (Daily)")
FROM JSON (wrap on) "root" PK "__KEY_root";

 

The connection is established; however I am getting the data in the below format.

Ryan101_0-1785335850317.pngRyan101_1-1785335874500.png

Ryan101_2-1785335926784.png

 

Whereas, I need the data in a single normalized table as below columns, kindly help.

Date Open High Low Close Volume  

Labels (2)
Labels
184 Views
2 Replies
BIAKS
Contributor III
Contributor III
Thursday

Hello, something like below should work:

// 1. Define your date range boundaries
LET vStartDate = Num(Date#('2026-07-20', 'YYYY-MM-DD'));
LET vEndDate   = Num(Date#('2026-07-24', 'YYYY-MM-DD'));

// 2. Loop through each date in the range
FOR vDateNum = vStartDate TO vEndDate

    // Format the current loop date to match your API key format
    LET vCurrentDate = Date($(vDateNum), 'YYYY-MM-DD');

    // 3. Load the data dynamically for the specific date
    TimeSeriesData:
    LOAD 
        '$(vCurrentDate)'  AS [Date],
        [1. open]          AS [Open], 
        [2. high]          AS [High], 
        [3. low]           AS [Low], 
        [4. close]         AS [Close], 
        [5. volume]        AS [Volume];
    SELECT 
        "1. open", 
        "2. high", 
        "3. low", 
        "4. close", 
        "5. volume"
    FROM "$(vCurrentDate)" FK "__FK_$(vCurrentDate)";

NEXT vDateNum;
109 Views
0 Likes
marksouzacosta
MVP
MVP
Thursday

Hi @Ryan101 ,

Can you share the REST API that you are using with us? Is that something public?
I'm also missing the rest of your Load Script. Can you please share your full code?

 

Regards,

Mark Costa

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

63 Views
0 Likes