Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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;
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;
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;
Loved your solution, Way better than mine!
Always learning here!
Thanks man
No a problem my friend
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!
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;
Great!!! thank you!!!!