topic Re: posting in QlikView

posting

Sat, 10 Sep 2011 09:15:35 GMT

hs case:

product posting qty

AAA 10/11/2010        10
BBB 05/11/2010         5
AAA      05/02/2010      2
BBB 10/11/2010 5

I need to create a FIELDS contains the zero value if
the previous posting was null and the posting must be
start from 01/11/20010.
Thanks
regards,
K.

posting

Sun, 11 Sep 2011 10:09:27 GMT

I qould to obtain something like this:

load
date(makedate(2011, 11,1)+recno()-1) as posting
autogenerate 15 ;

Load * INLINE [
product , posting, Qt
AAA , 04/11/2010 ,5
AAA , 10/11/2010 , 10
]:

results I would to obtain with new fields(newItem ,newPosting ,new Qt)

newItem newPosting new Qt
AAA     01/11/2010   0
AAA     02/11/2010   0
AAA     03/11/2010   0
AAA     04/11/2010   5
AAA     05/11/2010   0
AAA     06/11/2010   0
AAA     07/11/2010   0
AAA     08/11/2010   0
AAA     09/11/2010   0
AAA     10/11/2010   10
AAA     11/11/2010   0
AAA     12/11/2010   0
AAA     13/11/2010   0
........

Thanks
Regards
K.

Re: posting

Miguel_Angel_Baeyens — Sun, 11 Sep 2011 11:07:56 GMT

Hi,

Use the Previous() function to get the previous record values

If(Len(Previous(Qt)) > 0,  Qt, 0) AS Qt

Hope that helps,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: posting

Mon, 12 Sep 2011 08:18:29 GMT

I Attached a file.. the result doesn't match

Re: posting

swuehl — Mon, 12 Sep 2011 08:51:25 GMT

Hi k_l_a,

maybe like:

AA:

Load * INLINE [

product , posting , Qt

AAA , 04/11/2010 , 5

AAA , 10/11/2010 , 10

];

right join (AA) load

date(makedate(2010, 11,1)+recno()-1) as posting

autogenerate 15 ;

bb:

load

posting as newposting, Qt as newQT,

if(isnull(product),peek(newProduct),product) as newProduct

resident AA order by product desc;

Drop table AA ;

I merged the calendar to your AA table (maybe that is not the best idea, but just to demonstrate), then we have all the dates to go through and use peek() to fill in the missing product names (if we have a fixed name, something like

'AAA' as newProduct

will do also.

I am not sure how you would like to handle the product names when there are multiple products, but above reproduces your sample.

Regards,

Stefan

Re: posting

Mon, 12 Sep 2011 09:06:14 GMT

In fact it doesn't work if if I add an other product:

oad * INLINE [

product , posting , Qt

AAA , 04/11/2010 , 5

AAA , 10/11/2010 , 10

BB , 02/11/2010,11

BB , 08/11/2010,15

];

Re: posting

swuehl — Mon, 12 Sep 2011 09:09:23 GMT

Yes,

but how do you want to handle this?

In your original post, the product name was set with a name 'AAA' even before the first posting for that product.

So how should the table look like with multiple products (and possible intersecting periods) ?

Re: posting

Mon, 12 Sep 2011 09:40:48 GMT

The target is to handle all products and to create a new posting date
thath contains all posting generate by merging Calendar Masterand old posting field

The result I need from the Inline I posted is:

AAA 01/11/2010   0
AAA 02/11/2010   0
AAA 03/11/2010   0
AAA 04/11/2010   5
AAA 05/11/2010   0
AAA 06/11/2010   0
AAA 07/11/2010   0
AAA 08/11/2010   0
AAA 09/11/2010   0
AAA 10/11/2010    10
........

BB 01/11/2010   0
BB 02/11/2010   11
BB 03/11/2010   0
BB 04/11/2010   5
BB 05/11/2010   0
BB 06/11/2010   0
BB 07/11/2010   0
BB 08/11/2010   15
BB 09/11/2010   0
BB 10/11/2010   0

.......

Re: posting

swuehl — Mon, 12 Sep 2011 09:51:28 GMT

If you just need to display the data like you posted above, I think the solution could just be a separate calendar to draw your data against, like in attached sample.

Or do you really need to generate the tables in your data model?

Regards,

Stefan

Re: posting

Mon, 12 Sep 2011 10:15:25 GMT

Is correct "virtually" becouse the Qt are create in the object not in the field. The zero value does not in the new field but is created in the object by if statement..

I need the field becouse I wil use the new field wty and Date to other operations.

Thanks

Re: posting

swuehl — Mon, 12 Sep 2011 10:42:06 GMT

Ok,

I understood (so just for curiosity: what are you calculating that you need a value of 0 in your data model tables for each other day?)

I created a loop in the script over all product names, then create the calendar load within the loop and a lookup for the qty values.

This is my last try,

Stefan

Re: posting

Mon, 12 Sep 2011 12:19:02 GMT

Interesting ... probabil is the solution but I need to check in the loop over 10000 product ...

Re: posting

swuehl — Mon, 12 Sep 2011 12:50:35 GMT

Well, I haven't said that this is performing well.

Maybe this is faster (not using Lookup):

INPUT:

LOAD *, product & posting as ProductPosting;

Load * INLINE [

product , posting , Qt

AAA , 04/11/2010 , 5

AAA , 10/11/2010 , 10

BB , 02/11/2010,11

BB , 08/11/2010,15

];

ProductNamesTable:

LOAD Distinct product as ProductNames

RESIDENT INPUT;

FOR i = 1 TO NoOfRows('ProductNamesTable')

SET vTempFieldName = FieldValue('ProductNames', $(i));

RESULT:

LOAD

$(vTempFieldName) as newProduct,

date(makedate(2010, 11,1)+recno()-1) as newPosting

autogenerate 15 ;

left join (RESULT) LOAD posting as newPosting, product as newProduct, Qt as newQT

resident INPUT;

Re: posting

Tue, 13 Sep 2011 09:52:16 GMT

Ok. The script you suggest generate over 5.000.000 of rows but is correct.

No memory crash.

Many Thanks

Regards,

Kla

posting

swuehl — Tue, 13 Sep 2011 09:56:03 GMT

Good to hear!

(Regarding the 5 M rows I assume that's because of your Product Count X CalendarDay Count, right? Or are you saying that you expected a much smaller row number?)

posting

Fri, 23 Sep 2011 10:55:00 GMT

Dear friend of QV,

I need to add some dimensions:

lot no and location.

the result for Item is correct but now I need to add the result for Location and lot...

Load * INLINE [

product , posting , Qt , location, lot

AAA , 04/11/2010 , 5 , ita , 123
AAA , 10/11/2010 , 10 , fra , 107
AAA , 14/11/2010 , 25 , ita , 123
BB , 02/11/2010,11 , ita, 875
BB , 08/11/2010,15 , ger, 41

];

Re: posting

swuehl — Fri, 23 Sep 2011 16:41:46 GMT

Well, if I understood you correctly, just replace the last left join with

left join (RESULT) LOAD posting as newPosting, product as newProduct, Qt as newQT, location as newLocation, lot as newLot

resident INPUT;

posting

Sat, 24 Sep 2011 18:35:23 GMT

Please to build the progressive sum I need to use this script:

inv:
load * INLINE [

post, item , QTY , location , lot

01/01/2011 , AAA, 100, ita , 123
05/01/2011 , CCC, 26 , ita, 852
01/01/2011 , CCC, -4 , ita ,900
25602/01/2011 , AAA ,-16 , fra, 40
03/01/2011, AAA, -4 , ita. 1028
05/01/2011, FFF,70 , eng. 700
] ;

pk:
load
post as pos
, item as itm,
QTY as qq
resident inv

ORDER BY item , post ;

drop table inv ;

load
pos, itm ,
numsum(peek('QTYTOTAL') , qq) as QTYTOTAL
resident pk ;

drop table pk

But I'm not able to add the lot and Location dimensions to create the correct progressive sum and correct group of data field Location and lot

After I have to build the zero value as you suggest...

Thanks

thanks

posting

swuehl — Sun, 25 Sep 2011 13:51:50 GMT

Could you give an example of how the result should look like with the above table as input? Do you still want or need all be done in the script?

Re: posting

Fri, 30 Sep 2011 13:16:58 GMT

Please start from the file I attached. The data was built in progress way..

In this case I have a dimension Location.. How I have to menage the dimension?