topic Re: Cntd...from #747675 How to split a single row in QlikView

Cntd...from #747675 How to split a single row

senarath — Tue, 07 Apr 2015 12:43:32 GMT

Hi,

Further to above thread, I'm trying to load data from a file and does not work.

It works fine when load inline as below.

Budget:

Load Factory,Customer,Area,Amount,Month,Amount2/DaysInMonth As Amount2, Date(Date#(Month,'MMM-YY') + IterNo() - 1) As Date

While IterNo() <= DaysInMonth;

Load Factory,Customer,Area,Month, Day(MonthEnd(Date#(Month,'MMM-YY'))) As DaysInMonth, Amount, Amount2 Inline [

Factory,Customer,Area,Month,Amount, Amount2

FactoryA,JIT customers,NorthArea,Feb-15,1600.00, 175

FactoryB,Direct customers,NorthArea,Feb-15,1750.00, 140];

DOES NOT upload the data when I tried the same from a file as below. Script completes with no values

Budget:

Load Factory,Customer,Area,Amount,Month,Amount2/DaysInMonth As Amount2, Date(Date#(Month,'MMM-YY') + IterNo() - 1) As Date

While IterNo() <= DaysInMonth;

Load Factory,Customer,Area,Month, Day(MonthEnd(Date#(Month,'MMM-YY'))) As DaysInMonth, Amount, Amount2

FROM

[Budget_L_2015-16.xlsx]

(ooxml, embedded labels, table is HK);

Am I missing something here. Script completes but no data picked up

thanks

Re: Cntd...from #747675 How to split a single row

robert_mika — Tue, 07 Apr 2015 13:01:58 GMT

Could you attached your xlsx file?

Re: Cntd...from #747675 How to split a single row

Gysbert_Wassenaar — Tue, 07 Apr 2015 13:43:59 GMT

Possibly your excel Month values are not strings, but dates. The date# function wouldn't work then so DaysInMonth becomes a string value and IterNo() <= DaysInMonth will be a number to text comparison with the result that no records will be loaded.

Re: Cntd...from #747675 How to split a single row

senarath — Wed, 08 Apr 2015 00:15:39 GMT

Hi Gysbert,

You are correct. In my excel, date format was set as text and now it's working fine.

Thanks for your expert advise as usual and for your your time.

thanks

Senarath

Re: Cntd...from #747675 How to split a single row

senarath — Wed, 08 Apr 2015 00:16:44 GMT

Hi Robert,

Thanks for th reply. Before upload the file, I tried what Gysbert had mentioned and it worked.

thanks for your time.

Rgds,

Senarath