Solved

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Solved

Re: Filter Multiple Excel Rows in QV

Gysbert_Wassenaar — Mon, 21 Mar 2016 16:02:03 GMT

Perhaps like this:

FILTERS:
LOAD
ID_INDICATOR,
ID_CURRENCY,
ID_TIME,
ID_VERSION

FROM APP.xls (ooxml, embedded labels, table is FILTERS);

INDICATOR:
LOAD
%KEY_INDICATOR,
ID_INDICATOR,
INDICATOR
RESIDENT TMP_INDICATOR
WHERE EXISTS(ID_INDICATOR);
DROP TABLE TMP_INDICATOR;

CURRENCY:
LOAD
%KEY_CURRENCY,
ID_CURRENCY,
CURRENCY
RESIDENT TMP_CURRENCY
WHERE EXISTS(ID_CURRENCY);
DROP TABLE TMP_CURRENCY;

TIME:
LOAD
%KEY_TIME,
ID_TIME,
TIME
RESIDENT TMP_TIME
WHERE EXISTS(ID_TIME);
DROP TABLE TMP_TIME;

VERSION:
LOAD
%KEY_VERSION,
ID_VERSION,
VERSION
RESIDENT TMP_VERSION
WHERE EXISTS(ID_VERSION);
DROP TABLE TMP_VERSION;

Re: Filter Multiple Excel Rows in QV

dveloz17 — Mon, 21 Mar 2016 18:56:15 GMT

Hello microwin,

This would be a traditional way with which you could keep track of each table as it would generate one qv for each filter. You should only have encuenta the number of records smash for performance benchmarking application.

// Tabla de Filtros

FILTERS:

LOAD

ID_INDICATOR,

ID_CURRENCY,

ID_TIME,

ID_VERSION

FROM FILTERS.xlsx(ooxml, embedded labels, table is FILTERS);

LET vS_TotalFilas = NoOfRows('FILTERS')-1;

IF( $(vS_TotalFilas) > 0 ) THEN

FOR i=0 TO $(vS_TotalFilas)

// Asignación de Filtros

LET V_INDICATOR = PEEK('ID_INDICATOR',$(i),'FILTERS');

LET V_CURRENCY = PEEK('ID_CURRENCY',$(i),'FILTERS');

LET V_TIME = PEEK('ID_TIME',$(i),'FILTERS');

LET V_VERSION = PEEK('ID_VERSION',$(i),'FILTERS');

TRACE **********************************************;

TRACE ************ Se genera la tablas: ************;

TRACE ************ ID_INDICATOR: $(V_INDICATOR) ************;

TRACE ************ CURRENCY: $(V_CURRENCY) ************;

TRACE ************ TIME: $(V_TIME) ************;

TRACE ************ VERSION: $(V_VERSION) ************;

// Now I have the TABs with the DETAIL tables:

INDICATOR:

LOAD

%KEY_INDICATOR,

ID_INDICATOR,

INDICATOR

RESIDENT TMP_INDICATOR

WHERE ID_INDICATOR='$(V_INDICATOR)';

DROP TABLE TMP_INDICATOR;

CURRENCY:

LOAD

%KEY_CURRENCY,

ID_CURRENCY,

CURRENCY

RESIDENT TMP_CURRENCY

WHERE ID_CURRENCY='$(V_CURRENCY)';

DROP TABLE TMP_CURRENCY;

TIME:

LOAD

%KEY_TIME,

ID_TIME,

TIME

RESIDENT TMP_TIME

WHERE ID_TIME='$(V_TIME)';

DROP TABLE TMP_TIME;

VERSION:

LOAD

%KEY_VERSION,

ID_VERSION,

VERSION

RESIDENT TMP_VERSION

WHERE ID_VERSION='$(V_VERSION)';

DROP TABLE TMP_VERSION;

// Now I have the TAB with the FACT table:

TMP2_BUDGET:

LOAD

KEY_W1 AS %KEY_INDICATOR,

KEY_W2 AS %KEY_CURRENCY,

KEY_W3 AS %KEY_TIME,

KEY_W4 AS %KEY_VERSION,

DATA AS VALUE

RESIDENT TMP_BUDGET

WHERE EXISTS(%KEY_INDICATOR,KEY_W1)

AND EXISTS(%KEY_CURRENCY,KEY_W2)

AND EXISTS(%KEY_TIME,KEY_W3)

AND EXISTS(%KEY_VERSION,KEY_W4);

STORE TMP2_BUDGET INTO BUDGET_$(V_INDICATOR).qvd (qvd);

DROP TABLES TMP2_BUDGET,INDICATOR,CURRENCY,TIME,VERSION;

LET V_INDICATOR = NULL();

LET V_CURRENCY = NULL();

LET V_TIME = NULL();

LET V_VERSION = NULL();

DROP TABLE FILTERS;

LET vS_TotalFilas = NULL();

BUDGET:

LOAD

FROM BUDGET_*.qvd (qvd);

TRACE *** Se genero la tabla con: $(vS_TotalFilas) Registros de Filtros ***;

ELSE

TRACE ************ No existen Filtros en la Tabla de FILTERS ************;

ENDIF;

topic Re: Filter Multiple Excel Rows in QV in QlikView

Solved

Re: Filter Multiple Excel Rows in QV

Re: Filter Multiple Excel Rows in QV