Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
I have to develop a Power BI report based on SAP HANA data. There are 5 tables being used to fetch the data i.e. MATDOC, MAKT, LFA1, USER_ADD and 1 ZTable.
The data count is roughly around 3 lakhs.
Objective:
The objective is to get the data filtered during the run time. We want to get only last 3 months data in the data set. The issue is mentioned in BOLD.
SELECT
"WERKS" Plant,
"MBLNR" Mat_Doc_No,
"MJAHR" Mat_Doc_Year,
"BLDAT" Mat_Doc_Date,
"BUDAT" Mat_Doc_Post_Date,
"USNAM" SAP_ID,
"CPUDT" Created_On,
"CPUTM" Created_Time,
"EBELN" PO_No,
"EBELP" PO_Line_Item,
"LIFNR" Vendor_Code,
"MATNR" Mat_Code,
"ERFME" Qty,
"ERFMG" UoM,
"DMBTR" Amount_LC,
"LGORT" Storage_Location,
"ABLAD" Un_Loading_At,
"XBLNR" Delivery_Note,
"BKTXT" Header_Text,
"BWART" Movement_Type,
"LFBNR" Reference_Doc,
"LFPOS" Reference_Doc_Item,
"SMBLN" Ref_Mat_Doc,
"SMBLP" Ref_Mat_Doc_Item
FROM "SAPPRD"."MATDOC"
WHERE "BUDAT" > "Current_Date - 90 days"
AND "WERKS" IN ('1010','1011','1012')
AND "EBELN" IS NOT NULL AND "EBELN" <> ''
AND "BWART" IN ('101','102')
Please suggest
Thanks in advance
Solved! Go to Solution.
@Anonymous,
You can directly put the above statement into advanced options during the import process.
If the Mat_Doc_Post_Date field is date type, your condition should be as follows.
WHERE "BUDAT" > Current_Date - 90 or WHERE "BUDAT" > ADD_DAYS(Current_Date,-90)
There is a blog about date time functions in SAP hana for your reference.
https://sapstudent.com/hana/sql-datetime-functions-in-sap-hana
Regards,
Lydia
@Anonymous,
You can directly put the above statement into advanced options during the import process.
If the Mat_Doc_Post_Date field is date type, your condition should be as follows.
WHERE "BUDAT" > Current_Date - 90 or WHERE "BUDAT" > ADD_DAYS(Current_Date,-90)
There is a blog about date time functions in SAP hana for your reference.
https://sapstudent.com/hana/sql-datetime-functions-in-sap-hana
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |