topic Re: How to copy values to next 6 fields of a table in QlikView

How to copy values to next 6 fields of a table

Fri, 21 Nov 2014 07:33:11 GMT

14.08.

Value Hero

383,52

Hi there,

i am new to qlikview and i hope anybody can help me again.

I want to build something like this:

If tmpArt = ValueHero, then copy the value from tmpTeilUmsatz to the next 6 following fields.

i.e.:

14.08. Value Hero 383,52

15.08.

Value Hero

383,52

16.08.

Value Hero

383,52

17.08.

Value Hero

383,52

18.08.

Value Hero

383,52

19.08.

Value Hero

383,52

20.08. Value Hero 383,52

Thanks and best regards,

Sabrina

Re: How to copy values to next 6 fields of a table

MK_QSL — Fri, 21 Nov 2014 07:36:40 GMT

Can you please upload the result you are expecting in excel file ?

Re: How to copy values to next 6 fields of a table

Fri, 21 Nov 2014 07:43:26 GMT

Mapping1:

mapping load

Datum,

Sum(Umsatz)

resident Deals_Liste

group by Datum;

Mapping2:

mapping load

Datum,

Art

resident Deals_Liste;

Plattformen_Details:

LOAD Plattform as Plattform,

PlattformVJ as Plattform_VJ,

Datum,

[Artikel Online] as [Artikel Online],

if(ApplyMap('Mapping2',Datum) = 'Value Hero', ApplyMap('Mapping1',Datum)/7 ) as Division,

[Umsatz in € gesamt] ,

[Verkaufte Artikel] as [Verkaufte Artikel],

[Umsatz in € gesamt] as [Umsatz in € gesamt],

DDMM as DDMM

FROM

(ooxml, embedded labels, table is Qlik_2.de);

tmpDeals:

LOAD

Datum,

if(ApplyMap('Mapping2',Datum) <> 'Value Hero' and ApplyMap('Mapping2',Datum) <> 'Daily Deal',0, ApplyMap('Mapping2',Datum) ) as tmpArt,

if(ApplyMap('Mapping2',Datum) ='Value Hero', ApplyMap('Mapping1',Datum) / 7, 0) as tmpTeilUmsatz

resident Plattformen_Details;

Re: How to copy values to next 6 fields of a table

Fri, 21 Nov 2014 07:54:12 GMT

Or do you need the excel file i am loading from?

Re: How to copy values to next 6 fields of a table

MK_QSL — Fri, 21 Nov 2014 08:20:27 GMT

Your script will not help. Upload the excel file please

Re: Re: How to copy values to next 6 fields of a table

Fri, 21 Nov 2014 08:30:41 GMT

Here they are and thanks a lot!!!

Re: How to copy values to next 6 fields of a table

Fri, 21 Nov 2014 09:52:43 GMT

Any Idea?

Re: How to copy values to next 6 fields of a table

Fri, 21 Nov 2014 11:21:45 GMT

Nobody can help me?

I'm racking my head for the last days and i do not go on. 😞

Re: How to copy values to next 6 fields of a table

puttemans — Fri, 21 Nov 2014 11:51:11 GMT

Hi,

I didn't work out in detail, but roughly maybe this could work :

1/first you only select those lines where the Value= Value Hero

2/then you autonumber the lines you selected

3/then you build a repetition for each line in order to create your dates

For line 1 to x;

Set a=null();

For a = 1 to 7;

Load

Num#(Date-1)+$(a) as Date;

Umsatz as Umsatz

Next;

While you make sure to concatenate all lines. I think that should bring you pretty close to your desired result.

Re: How to copy values to next 6 fields of a table

simenkg — Fri, 21 Nov 2014 12:00:29 GMT

You can use the Peek() to do this.

is it allways 6 values or is it allways all the lines until a new tmpArt?

I'm going to assume the last one because it is a little simpler to implement.

Something like this.

Load .

if(isnull(tmpArt), Peek(newtmpArt),tmpArt) as newtmpArt,

if(peek(newtmpArt)='ValueHero',Peek(tmpTeilUmsatz),tmpTeilUmsatz) as tmpTeilUmsatz

from Table....

Re: How to copy values to next 6 fields of a table

MarcoWedel — Sat, 22 Nov 2014 23:25:07 GMT

Hallo Sabrina,

one possible solution could be:

tabTemp:
LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/662458-138544/Mappe2.xlsx] (ooxml, embedded labels, table is Tabelle1);
RENAME Field Start_Datum to Start;

NoConcatenate
tabSales:
LOAD Date(Start+IterNo()-1) as Start_Datum,
    *
While IterNo()<=Days or IterNo()=1;
LOAD *,
    Previous(Start)-Start as Days
Resident tabTemp
Order By Start desc;

DROP Table tabTemp;
DROP Field Start;

hope this helps

regards

Marco

Re: Re: How to copy values to next 6 fields of a table

MarcoWedel — Sat, 22 Nov 2014 23:29:32 GMT

if you want to restrict the copies to specific field values, you could try like:

tabTemp:
LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/662458-138544/Mappe2.xlsx] (ooxml, embedded labels, table is Tabelle1);
RENAME Field Start_Datum to Start;

NoConcatenate
tabSales:
LOAD Date(Start+IterNo()-1) as Start_Datum,
     *
While IterNo()<=Days and Art='Value Hero' or IterNo()=1;
LOAD *,
     Previous(Start)-Start as Days
Resident tabTemp
Order By Start desc;

DROP Table tabTemp;
DROP Field Start;

hope this helps

Gruß

Marco

Re: How to copy values to next 6 fields of a table

Mon, 24 Nov 2014 07:10:37 GMT

Hi,

its always the next 6 dates:

Excel Row: 12.08.2014 555,55

To load: 13.08.2014 555,55

14.08.2014 555,55

15.08.2014

555,55

16.08.2014

555,55

17.08.2014

555,55

18.08.2014

555,55

Thanks!!!!

Re: How to copy values to next 6 fields of a table

Mon, 24 Nov 2014 08:39:00 GMT

Hi Marco,

thank you so much for your help!

I dont have to fill all the lines until a new "Art", but only the next 6 rows (fields) after a Value Hero.
Any idea how to implement this to your code?

Best regards,

Sabrina

Re: How to copy values to next 6 fields of a table

MarcoWedel — Mon, 24 Nov 2014 08:51:34 GMT

While IterNo()<=RangeMin(6,Days) and Art='Value Hero' or IterNo()=1;

Re: How to copy values to next 6 fields of a table

simenkg — Mon, 24 Nov 2014 10:16:01 GMT

if(isnull(tmpArt), Peek(newtmpArt),tmpArt) as newtmpArt,

if(tmpArt='ValueHero',0,Rangesum(Peek(Counter),1)) as Counter,

if(peek(newtmpArt)='ValueHero' and Peek(Counter)<6,Peek(tmpTeilUmsatz),tmpTeilUmsatz) as tmpTeilUmsatz