topic Qlikview loading Excel line problem in QlikView

Qlikview loading Excel line problem

alexandermllr — Tue, 29 Aug 2017 08:44:42 GMT

Hello,

I have a problem with loading this Excel file in Qlikview.

How can I load every Details line for the Product?

I hope someone can help me!

Thanks

Re: Qlikview loading Excel line problem

antoniotiman — Tue, 29 Aug 2017 08:58:01 GMT

Hi Alexander,

LOAD If(IsNull(Product),Peek(Product),Product) as Product,
If(IsNull(Ammount),Peek(Ammount),Ammount) as Ammount,Details;
LOAD Product,
Ammount,
Details
FROM

(ooxml, embedded labels, table is Tabelle1);

Regards,

Antonio

Re: Qlikview loading Excel line problem

techvarun — Tue, 29 Aug 2017 08:59:00 GMT

Directory;

LOAD Product,

Ammount,

Details

FROM

Mappe1.xlsx

(ooxml, embedded labels, table is Tabelle1, filters(

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null))

));

Re: Qlikview loading Excel line problem

techvarun — Tue, 29 Aug 2017 09:01:15 GMT

I achieved this through enable transformation import assistant

Re: Qlikview loading Excel line problem

alexandermllr — Tue, 29 Aug 2017 09:13:09 GMT

Thank you

But then i have the Ammount and Product 4 or 5 times.

Is it possible to to make for every Details line an extra field?

Product, Ammount, Details 1, Details 2, Details 3....

Re: Qlikview loading Excel line problem

antoniotiman — Tue, 29 Aug 2017 09:26:22 GMT

Try like this

Table:
LOAD If(IsNull(Product),Peek(Product),Product) as Product,
If(IsNull(Ammount),Peek(Ammount),Ammount) as Ammount,Details;
LOAD Product,
Ammount,
Details
FROM

(ooxml, embedded labels, table is Tabelle1);
Generic LOAD Product,Details,Ammount Resident Table;
Drop Table Table;

Re: Qlikview loading Excel line problem

alexandermllr — Tue, 29 Aug 2017 11:39:13 GMT

I want to have a table like this. Attached the file.

Thanks for your help!

Re: Qlikview loading Excel line problem

antoniotiman — Tue, 29 Aug 2017 16:15:37 GMT

Try this

Table:
LOAD If(IsNull(Product),Peek(Product),Product) as Product,Details1,
If(IsNull(Ammount),Peek(Ammount),Ammount) as Ammount,Details;
LOAD Product,'Dectails'&Autonumber(Details) as Details1,
Ammount,
Details as Details
FROM [Mappe2.xlsx]
(ooxml, embedded labels, table is Tabelle1);
Generic LOAD Product,Ammount,Details1,Details Resident Table;
Drop Table Table;

Re: Qlikview loading Excel line problem

alexandermllr — Wed, 30 Aug 2017 13:38:41 GMT

Thank you!

That worked