Create a different table that the table source with Qlikview script

[juagarti]

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

[santiago_respane]

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;

[sunny_talwar]

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;

[santiago_respane]

Loved your solution, Way better than mine!

Always learning here!

Thanks man

[sunny_talwar]

No a problem my friend

[juagarti]

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!

[sunny_talwar · Accepted solution]

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;

[juagarti]

Great!!! thank you!!!!