topic Re: cross table and flag qliksense in App Development

cross table and flag qliksense

Aspiring_Developer — Tue, 09 Feb 2021 13:35:44 GMT

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

@Kushal_Chawda

Re: cross table and flag qliksense

QFabian — Wed, 10 Feb 2021 02:14:09 GMT

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;

Re: cross table and flag qliksense

Aspiring_Developer — Wed, 10 Feb 2021 07:51:19 GMT

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

Re: cross table and flag qliksense

QFabian — Wed, 10 Feb 2021 16:00:34 GMT

CrossTable(PERIOD_AUX, FLAG, 29)