Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
Please help me with the below:-
I have below table fetched from SQL
LIB CONNECT TO 'HANA_PROD ;
LOAD "SOURCEID",
"ENTITY",
"BUKRS",
"BUTXT",
"LANDX",
"REGION",
"DIVISION",
"BLART",
"BELNR",
"AUGDT",
"AUGBL",
"KUNNR",
"BLDAT",
"CPUDT",
"LIFNR",
"NAME1",
"KTOKK",
"TXT30",
"NBOFDAYS",
"BUZEI",
"SGTXT",
"HKONT",
"TXT50",
"BUDAT",
"TOTAL",
"WAERS",
"TOTAL_L",
"HWAER",
"AMOUNT_EUR",
"PERIOD_1",
"PERIOD_2",
"PERIOD_3",
"PERIOD_4",
"PERIOD_5",
"PERIOD_6",
"PERIOD_7",
"PERIOD_8",
"PERIOD_9",
"PERIOD_10",
"PERIOD_11",
"PERIOD_12",
"PERIOD_13",
"CATEGORY";
SQL SELECT "SOURCEID",
"ENTITY",
"BUKRS",
"BUTXT",
"LANDX",
"REGION",
"DIVISION",
"BLART",
"BELNR",
"AUGDT",
"AUGBL",
"KUNNR",
"BLDAT",
"CPUDT",
"LIFNR",
"NAME1",
"KTOKK",
"TXT30",
"NBOFDAYS",
"BUZEI",
"SGTXT",
"HKONT",
"TXT50",
"BUDAT",
"TOTAL",
"WAERS",
"TOTAL_L",
"HWAER",
"AMOUNT_EUR",
"PERIOD_1",
"PERIOD_2",
"PERIOD_3",
"PERIOD_4",
"PERIOD_5",
"PERIOD_6",
"PERIOD_7",
"PERIOD_8",
"PERIOD_9",
"PERIOD_10",
"PERIOD_11",
"PERIOD_12",
"PERIOD_13",
"CATEGORY"
FROM "_SYS_BIC"."ZPC.ZPC_CALC_VIEW.KPI302_18/KPI302_RATIO_V2";
The values for the periodes are dynamic at backend , eg if periode_1 is Jan2021, when we the March month comes, the
value for "PERIOD_1" will become Feb 2021
The value inside all the "Periode" fields is either 0 or 1
My requirement is to caluclate the Sum("AMOUNT_EUR") for each period when the value is 1.
I am not able to understand how can i achieve this in qliksese. Please please help.
Thank You
HI @Aspiring_Developer , i couldnt test it, but try this please, the main idea is do the crosstable to get the differents PERIOD fields name as values , and then use that values to create the differents monthname as you wish.
Maybe can be a little guide or option for your requirement.
CrossTable(PERIOD_AUX, FLAG, 3)
TableAux:
LOAD
"SOURCEID",
"ENTITY",
"BUKRS",
"BUTXT",
"LANDX",
"REGION",
"DIVISION",
"BLART",
"BELNR",
"AUGDT",
"AUGBL",
"KUNNR",
"BLDAT",
"CPUDT",
"LIFNR",
"NAME1",
"KTOKK",
"TXT30",
"NBOFDAYS",
"BUZEI",
"SGTXT",
"HKONT",
"TXT50",
"BUDAT",
"TOTAL",
"WAERS",
"TOTAL_L",
"HWAER",
"AMOUNT_EUR",
"CATEGORY",
"PERIOD_1",
"PERIOD_2",
"PERIOD_3",
"PERIOD_4",
"PERIOD_5",
"PERIOD_6",
"PERIOD_7",
"PERIOD_8",
"PERIOD_9",
"PERIOD_10",
"PERIOD_11",
"PERIOD_12",
"PERIOD_13"
Resident YOURTABLE;
Data:
Load
some_Id_field,
monthname(addmonths(today(), -num#(subfield(PERIOD_AUX, '_', 2)))) as PERIOD,
AMOUNT_EUR as AMOUNT
Resident TableAux
Where
FLAG = 1;
Hello @QFabian ,
thank you for the response
I tried to implement your solution, however, i am facing below issues:-
1) When creatting created crosstaable, i did not got the right output:-
//-----Creating crosstable to get the Periode values in single column--//
CrossTable(PERIOD_AUX, FLAG, 3)
TableAux:
LOAD
"SOURCEID",
"ENTITY",
"BUKRS",
"BUTXT",
"LANDX",
"REGION",
"DIVISION",
"BLART",
"BELNR",
"AUGDT",
"AUGBL",
"KUNNR",
"BLDAT",
"CPUDT",
"LIFNR",
"NAME1",
"KTOKK",
"TXT30",
"NBOFDAYS",
"BUZEI",
"SGTXT",
"HKONT",
"TXT50",
"BUDAT",
"TOTAL",
"WAERS",
"TOTAL_L",
"HWAER",
"AMOUNT_EUR",
"CATEGORY",
"PERIOD_1",
"PERIOD_2",
"PERIOD_3",
"PERIOD_4",
"PERIOD_5",
"PERIOD_6",
"PERIOD_7",
"PERIOD_8",
"PERIOD_9",
"PERIOD_10",
"PERIOD_11",
"PERIOD_12",
"PERIOD_13"
Resident Data;
output:-
The new column 'PERIODE_AUX' and Flag should not display the above output.
Please help
CrossTable(PERIOD_AUX, FLAG, 29)