Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Reload Data by Using For Loop : Qliksense

Hello All,

I am extracting data from source system.

Requirement  :

1. I want to extract data for last 3 months 

Issue : 

Data Load fails while using dynamic variable in extraction 

Summary : 

Note : Date is maintained in the format of YYYYMM in source system

Due to huge size of data i cant execute all 3 months data in one time. Hence i have created FOR Loop to extract the data from source system.

I am extracting data based on Calendar month (YYYYMM)) format.

When i pass variable in where clause its ending up with error.

Could you please let me know what could be error here?

//Z0CALMONTH_CCMI_VAR001 = 202002

let v_current_month = Year(today()) & num(Month(Today()),'00');
let v_current_month_1 = Year(today()) & Num(Month(AddMonths(Today(),-1)),'00');
let v_current_month_2 = Year(today()) & Num(Month(AddMonths(Today(),-2)),'00');

Period_table:
Load * Inline [
month_value,
'$(v_current_month)'
'$(v_current_month_1)'
'$(v_current_month_2)'
];

For i=0 to 3;
let months=Peek(month_value,$(i),'Period_table');
LIB CONNECT TO 'SAPBWBEX_Prod';

[ZZ00SDSV01_SDSS_001]:
LOAD
[0COMP_CODE] as [Company code],
[0CALMONTH] as [Calendar Year/Month],
[COC1GKDA48088F3VR5VJX4RV6] as [No. of Sales Orders],
[CURRENCY001] as [CURRENCY001 No. of Sales Orders],
[UNIT001] as [UNIT001 No. of Sales Orders],
[0COMP_CODE__TEXT] as [Company code TEXT],
[0CALMONTH__TEXT] as [Calendar Year/Month TEXT];

SELECT [ZZ00SDSV01_SDSS_001]
DIMENSIONS (
[0COMP_CODE], //Company code
[0CALMONTH], //Calendar Year/Month
)
MEASURES (
[COC1GKDA48088F3VR5VJX4RV6], //No. of Sales Orders
)
UNITS (
[CURRENCY001.COC1GKDA48088F3VR5VJX4RV6], //CURRENCY001 No. of Sales Orders
[UNIT001.COC1GKDA48088F3VR5VJX4RV6], //UNIT001 No. of Sales Orders
)
VARIABLES (

[NAME=Z0CALMONTH_CCMI_VAR001, SIGN=I, OPTION=EQ, LOW= '$(months)'], //Calendar Year/Month - (MM.YYYY)


)

FROM [Z00SDSV01];

next i;


STORE * FROM [ZZ00SDSV01_SDSS_001] INTO [$(vG.ExtractPath)/SAPBWBEX/ZZ00SDSV01_SDSS_001.qvd];

 

Labels (1)
1 Reply
Highlighted

May be, instead of passing the, variable value in variable statement, pass it in the Where condition of the SQL query.

Put the LIB connection string before the Varaible creation, it avoids multiple time of connection to SAP.

In the For loop, also try with Fieldvalue(month_value,'$(i)') at peek function.

Try this : For i = 1 to 4.