topic Re: Create a different table that the table source with Qlikview script in QlikView

Create a different table that the table source with Qlikview script

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

Hello,

I have a QlikView Aplication that use a Oracle DB for get the data. In the DB I have one table like this:

In the aplication I need a talbe like this

How can I do this?

Thanks!!

Re: Create a different table that the table source with Qlikview script

santiago_respane — Fri, 29 Apr 2016 18:24:53 GMT

Hi Juan,

find attached solution take a look at the code and adapt it to your needs.

Solved via for loop with a nested while. Destination table with dummy is used to create empty table before starts loop.

Please let me know if this helps you and if you need anything else.

Kind regards,

Santiago

The solution code:

ORIGEN:

LOAD * INLINE [

Promotion,FechaA, FechaB,UR,Units

Proj,'01-Oct-2015','01-Jan-2016',Venta A, 2498

Proj,'01-Oct-2015','01-Jan-2016',Venta B, -5642498

];

DESTINATION_WITH_DUMMY:

LOAD * INLINE [

Promocion,Detalle, Unidades,Fecha

Dummy,Dummy,999,42856

];

LET v.Rows = NoOfRows('ORIGEN');

FOR i= 0 to v.Rows - 1

LET v.DateFrom = NUM(DATE#(PEEK('FechaA',$(i),'ORIGEN'),'DD-MMM-YYYY'));

LET v.DateTo = NUM(DATE#(PEEK('FechaB',$(i),'ORIGEN'),'DD-MMM-YYYY'));

LET v.UR = PEEK('UR',$(i),'ORIGEN');

LET v.Unidades = PEEK('Units',$(i),'ORIGEN');

DO WHILE (v.DateFrom < v.DateTo)

LET v.PromoCode = $(i) + 1;

CONCATENATE(DESTINATION_WITH_DUMMY)

LOAD

'Promocion ' & '$(v.PromoCode)' as Promocion,

'$(v.UR)' as Detalle,

$(v.Unidades) as Unidades,

DATE('$(v.DateFrom)') as Fecha

AutoGenerate(1);

LET v.DateFrom = NUM(AddMonths('$(v.DateFrom)',1));

LOOP;

NEXT;

//Delete dummy record

DESTINATION:

NOCONCATENATE

LOAD * RESIDENT DESTINATION_WITH_DUMMY WHERE Detalle <> 'Dummy';

DROP TABLE DESTINATION_WITH_DUMMY;

Re: Create a different table that the table source with Qlikview script

sunny_talwar — Fri, 29 Apr 2016 19:37:19 GMT

Another options:

Table:

LOAD * Inline [

Promotion, Fecha alta, Fecha baja, UR afectada, # de unidades

Proyecto AppToIP J-15PS41, 01/10/2015, 01/01/2016, Venta de producto cliente B, -24097

Proyecto AppToIP J-15PS41, 01/10/2015, 01/01/2016, Venta de producto cliente A, -426.6630075

];

FinalTable:

LOAD 'Promotion' & AutoNumber([UR afectada]) as Promotion,

[UR afectada] as detalle,

[# de unidades] as unidades,

AddMonths([Fecha alta], IterNo()-1) as FECHA_REPORT

Resident Table

While AddMonths([Fecha alta], IterNo()-1) <= [Fecha baja];

DROP Table Table;

Re: Create a different table that the table source with Qlikview script

santiago_respane — Fri, 29 Apr 2016 19:57:30 GMT

Loved your solution, Way better than mine!

Always learning here!

Thanks man

Re: Create a different table that the table source with Qlikview script

sunny_talwar — Fri, 29 Apr 2016 20:53:52 GMT

No a problem my friend

Re: Create a different table that the table source with Qlikview script

juagarti — Thu, 19 May 2016 14:17:21 GMT

Hello, sorry about the delay of my answer.

The problem that I found know, is that the table source are in a oracle DB, so when i make the select to consult the data I use this:

DATOS:

LOAD

PROMOTION,

fecha alta,

fecha baja,

UR afectada,

# de unidades;

SQL

SELECT * FROM DATOS;

How can I try to do in this case?

Thank you!

Re: Create a different table that the table source with Qlikview script

sunny_talwar — Thu, 19 May 2016 14:34:24 GMT

May be this:

DATOS:

LOAD

PROMOTION,

fecha alta,

fecha baja,

UR afectada,

# de unidades;

SQL

SELECT * FROM DATOS;

FinalDATOS:

LOAD 'Promotion' & AutoNumber([UR afectada]) as Promotion,

[UR afectada] as detalle,

[# de unidades] as unidades,

AddMonths([Fecha alta], IterNo()-1) as FECHA_REPORT

Resident DATOS

While AddMonths([Fecha alta], IterNo()-1) <= [Fecha baja];

DROP Table DATOS;

Re: Create a different table that the table source with Qlikview script

juagarti — Mon, 20 Jun 2016 12:17:15 GMT

Great!!! thank you!!!!