topic Two Files Loaded (To be used as dimension) in Water Cooler

Two Files Loaded (To be used as dimension)

hykal1993 — Mon, 14 Jan 2019 06:21:33 GMT

I have two files with the exact data fields (one is extracted on Jan 2018, another on Feb 2018). We would like to put the dates of extraction as the y-axis on the grid chart. What should I do, attached is the data.

Note: The fields are much more than the two attached and some of the field might be used for the colour of the bubble chart. Additionally, we would like to extend this to a 12 month trend.

Thank you so much for your help!

Re: Two Files Loaded (To be used as dimension)

Gysbert_Wassenaar — Tue, 15 Jan 2019 07:21:36 GMT

If you want to extract the period from the file name you can use the FileName() function in the load statement. Something like

MyData:
LOAD
   ...lots of fields...,
   Date#(SubField(FileName(),'.',1),'MMM YYYY') as Period
FROM
    [D:\MyExcelFiles\*.xlsx] (ooxml, ...etc);

Re: Two Files Loaded (To be used as dimension)

hykal1993 — Mon, 14 Jan 2019 10:31:36 GMT

Anything wrong with my code? It seems off and there's an error.

LOAD ID, 
     [Risk Type],
     Date#(SubFile(Jan 2018.xlsx,'.',1),'MMM YYYY') as Period
     
FROM
[C:\Users\haab\Desktop\Jan 2018.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Two Files Loaded (To be used as dimension)

Gysbert_Wassenaar — Mon, 14 Jan 2019 11:39:37 GMT

Try using the FileName() function instead of the name of the file.

Re: Two Files Loaded (To be used as dimension)

hykal1993 — Tue, 15 Jan 2019 06:49:14 GMT

Still there's an error.

LOAD ID,
     [Risk Type],
     Date#(SubFile(FileName(),'.',1),'MMM YYYY') as Period
     
FROM
[C:\Users\haab\Desktop\Feb 2018.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Two Files Loaded (To be used as dimension)

Gysbert_Wassenaar — Tue, 15 Jan 2019 07:22:09 GMT

Oops, typo. Try SubField instead of SubFile.

Re: Two Files Loaded (To be used as dimension)

hykal1993 — Tue, 15 Jan 2019 07:52:04 GMT

LOAD ID, 
     [Risk Type],
     Date#(SubField(FileName(),'.',1),'MMM YYYY') as Period
FROM
[C:\Users\haab\Desktop\Jan 2018.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD ID as [Feb ID], 
     [Risk Type] as [Feb Risk Type],
     Date#(SubField(FileName(),'.',1),'MMM YYYY') as Period
FROM
[C:\Users\haab\Desktop\Feb 2018.xlsx]
(ooxml, embedded labels, table is Sheet1);

Thanks, now there's no longer errors. However, I'm trying to build Grid Chart and the period (Y-Axis) is only showing Jan 2018. The Feb 2018 is missing.