Reload Data by Using For Loop : Qliksense

poojapalni1 — Thu, 23 Dec 2021 13:50:43 GMT

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];

Re: Reload Data by Using For Loop : Qliksense

BalaBhaskar_Qlik — Mon, 13 Apr 2020 07:54:45 GMT

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.

topic Re: Reload Data by Using For Loop : Qliksense in Connectivity & Data Prep

Reload Data by Using For Loop : Qliksense

Re: Reload Data by Using For Loop : Qliksense