Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Aspiring_Developer
Creator II
Creator II

cross table and flag qliksense

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

Aspiring_Developer_2-1612877394322.png

The value inside all the "Periode" fields is either 0 or 1

Aspiring_Developer_1-1612875007260.png

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

@Kushal_Chawda 

 

 

 
 
 
3 Replies
QFabian
Specialist II
Specialist II

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;

QFabian
Aspiring_Developer
Creator II
Creator II
Author

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:-

Aspiring_Developer_0-1612943390734.png

The new column 'PERIODE_AUX' and Flag should not display the above output.

 

Please help

 

QFabian
Specialist II
Specialist II

CrossTable(PERIOD_AUX, FLAG, 29)

QFabian