fill transformation for multiple files

[louisernould]

hi everybody,

i'm a newbee in Qlikview and I have a problem with the fill transformation.

here it is:

I want to analyse a just some lines in multiples excel files:  I have 50 files with quite the same name (test1.xls, test2.xls,...test50.xls and it is the same architecture in the files) and I want only the 3 first lines. So, I used "where RowNo()<3" at the end. No problem for this.

My problem appears because I have in my fist column one date (date1) for other dates (Date2) and when do the "fill transformation", the final table makes the fill transformation only for the first file and not for the other.

To resume: I have two steps I have to do:

- make a big table with the 3 first lines of multiples excels files

- fill the empty fields with the upper date

for example:

test1.xls

Date1	Date2	quantity
01/01/2014	01/01/2014	10
	02/01/2014	10
	03/01/2014	20

test2.xls

Date1	Date2	quantity
02/01/2014	02/01/2014	10
	03/01/2014	20
	04/01/2014	20

test3.xls

Date1	Date2	quantity
03/01/2014	03/01/2014	20
	04/01/2014	20
	05/01/2014	30

The final Table that qlikview gives me:

Date1	Date2	quantity
01/01/2014	01/01/2014	10
01/01/2014	02/01/2014	20
01/01/2014	03/01/2014	20
02/01/2014	02/02/2014	10
03/01/2014	03/01/2014	20

And what I really want:

Date1	Date2	quantity
01/01/2014	01/01/2014	10
01/01/2014	02/01/2014	20
01/01/2014	03/01/2014	20
02/01/2014	02/02/2014	20
02/01/2014	03/01/2014	20
02/01/2014	04/01/2014	30
03/01/2014	02/01/2014	30
03/01/2014	03/01/2014	30
03/01/2014	04/01/2014	40

My code is:

LOAD

     Date1,

     Date2,

     quantity

FROM

(biff, embedded labels, filters( Replace(1, top, StrCnd(null))))

where RowNo()<3;

I really hope it's enough clear, if it's not, don't hesitate, tell me and i will try to be more

I tried a lot of thing but now I don't have more ideas...

Thanks a lot

Louis

[MK_QSL · Accepted solution]

While load the files, you can replace Date1 field with below expressions...

If(IsNull(Date1) or Len(Trim(Date1))=0, Peek(Date1), Date1) as Date1,

[louisernould]

It's not working , its return me the same table.

But I think the problem is coming from the "where" and the "replace". I have the feeling that the qlikview is not doing the loop "replace", it's treating the first excel file ans not the other...

Is it possible to make a loop  with a "for each" ?

[MK_QSL]

Can you provide three four excel file with 10 lines each along with the script you are using?

[alexandros17]

Try with this code and let me know:

LOAD If(isnull(Date1), if(IsNull(Previous(Date1)), Previous(Previous(Date1)), Previous(Date1)), Date1) as Date1,

     Date2,

     quantity

FROM

AAAA.xlsx

(ooxml, embedded labels, table is Foglio1);

[louisernould]

Hi Alessandro, it's not working either.

(how can i upload some files ? I have a rar archive ready)

[alexandros17]

you said your code is:

LOAD

     Date1,

     Date2,

     quantity

FROM

(biff, embedded labels, filters( Replace(1, top, StrCnd(null))))

where RowNo()<3;

then transform it in:

LOAD If(isnull(Date1), if(IsNull(Previous(Date1)), Previous(Previous(Date1)), Previous(Date1)), Date1) as Date1,

     Date2,

     quantity

FROM

(biff, embedded labels, filters( Replace(1, top, StrCnd(null))))

where RowNo()<3;

[MarcoWedel]

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

[louisernould]

Hi !

I followed your advise in a different way: and it worked !!

Actually, when I did it, it didn't work, but I removed "where RowNo()<3", and now I have the totality of the date but the text under the line 3 appears... 😕
And when I put "where..." again, all the good dates disappear (and the text disappear too )

Do you know something else to say the same thing that "where..." ?

this code work but now I have all the field... and I want only 3 lines:

LOAD  If(IsNull(Date1) or Len(Trim(Date1))=0, Peek(Date1), Date1) as Date1,

     Date2,

     Quantity

FROM

(biff, embedded labels);

[MK_QSL]

Actually, can you simply attach your excel file rather than .rar... I can't uncompressed it....