topic Re: fill transformation for multiple files in QlikView

fill transformation for multiple files

louisernould — Mon, 01 Sep 2014 13:25:25 GMT

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

Re: fill transformation for multiple files

MK_QSL — Mon, 01 Sep 2014 13:30:24 GMT

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,

Re: fill transformation for multiple files

louisernould — Mon, 01 Sep 2014 13:40:55 GMT

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" ?

Re: fill transformation for multiple files

MK_QSL — Mon, 01 Sep 2014 13:43:44 GMT

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

Re: fill transformation for multiple files

alexandros17 — Mon, 01 Sep 2014 13:59:59 GMT

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);

Re: fill transformation for multiple files

louisernould — Mon, 01 Sep 2014 14:16:32 GMT

Hi Alessandro, it's not working either.

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

Re: fill transformation for multiple files

alexandros17 — Mon, 01 Sep 2014 14:21:35 GMT

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;

Re: fill transformation for multiple files

MarcoWedel — Mon, 01 Sep 2014 14:50:48 GMT

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

Re: fill transformation for multiple files

louisernould — Mon, 01 Sep 2014 15:10:59 GMT

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);

Re: fill transformation for multiple files

MK_QSL — Mon, 01 Sep 2014 15:13:28 GMT

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

Re: fill transformation for multiple files

louisernould — Mon, 01 Sep 2014 15:17:16 GMT

Hi again

Thank everybody for all your answers !!

I got it !! The problem was about RowNo() and RecNo()... and from Manish with his solution

The answer was here: rowno() and recno()

Have a nice day , and thanks again for the differents solutions you gave to me

Louis