topic Re: Need help in QlikView

Need help

Wed, 20 May 2015 14:42:43 GMT

Hi All,

I have have uploaded source file and the expected output file.

Please help me get the desired result.

My requirement is whenever the field US WD is zero, corresponding date should be last non zero(US WD)

date like below.

Date	US WD	Expected output
05/22/2015	1	05/22/2015
05/23/2015	0	05/22/2015
05/24/2015	0	05/22/2015
05/25/2015	0	05/22/2015
05/26/2015	1	05/26/2015

Any suggestions appreciated.

Thanks& Regards

Jyothi

Re: Need help

sunny_talwar — Wed, 20 May 2015 14:49:04 GMT

Try this script:

Table:

LOAD *,

If([US WD] = 0, Alt(Peek('Required'), DAY), DAY) as Required;

LOAD DAY,

[US WD]

FROM

Community_164851.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Need help

sunny_talwar — Wed, 20 May 2015 14:49:59 GMT

Output Table Box:

Re: Need help

Wed, 20 May 2015 14:54:52 GMT

Hi Sunindia,

Thanks for your reply.

How can i use the 'Required' field in the Peek function.?

I have only DAY and US WD fields in my source excel.

in Expected output excel i have manually entered 'Required' field..

Any suggestions?

Thanks

Re: Need help

sunny_talwar — Wed, 20 May 2015 14:56:41 GMT

I am using Alt function here which takes care of the required not being there the 1st time. After the first row is created, you have a required field now.

Does that make sense?

Re: Need help

sunny_talwar — Wed, 20 May 2015 15:01:16 GMT

Try this:

I am not using your output file. I am creating this in the script (Changed Required to ABC)

Table:

LOAD *,

If([US WD] = 0, Alt(Peek('ABC'), DAY), DAY) as ABC;

LOAD DAY,

[US WD]

FROM

Community_164851.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Need help

Wed, 20 May 2015 15:03:22 GMT

Thank you for making me understand

i will try the same and let you know.

Thanks.

Re: Need help

sunny_talwar — Wed, 20 May 2015 15:05:25 GMT

Sure thing.

Best,

Sunny

Re: Need help

Wed, 20 May 2015 15:15:32 GMT

Awesome! Its working. I have been struggling with this since 2 days. I did a mistake by uploading in community late

Thank You so much

Jyothi

Re: Need help

Wed, 20 May 2015 15:16:22 GMT

Really useful thanks

Re: Need help

sunny_talwar — Wed, 20 May 2015 15:18:39 GMT

Awesome

I am glad I was able to help you.

Best,

Sunny

Re: Need help

Tue, 26 May 2015 13:24:37 GMT

Hi sunindia,

Is it possible to add one more column like in the attached Excel sheet(ExpectedOutput_New)?

Please help...

Re: Need help

sunny_talwar — Tue, 26 May 2015 13:37:24 GMT

This is the column you need help with -> Req_WorkDay_Count?

Re: Need help

Tue, 26 May 2015 13:38:44 GMT

Yes...

Re: Need help

sunny_talwar — Tue, 26 May 2015 13:39:20 GMT

If yes, then try this script:

Table:

LOAD *,

[US WD] + Alt(Peek('Req_WorkDay_Count'), 0) as Req_WorkDay_Count;

LOAD *,

If([US WD] = 0, Alt(Peek('Required'), DAY), DAY) as Required;

LOAD DAY,

[US WD]

FROM

Community_164851.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Need help

Tue, 26 May 2015 14:25:29 GMT

Thanks So much Sunindia

also is it possible to begin the count from zero on every 1st day of the month..? any suggestions?

Re: Need help

sunny_talwar — Tue, 26 May 2015 14:35:12 GMT

Use this script

Table:

LOAD *,

If(MonthName = Peek('MonthName'), [US WD] + Alt(Peek('Req_WorkDay_Count'), 0), [US WD]) as Req_WorkDay_Count;

LOAD *,

If([US WD] = 0, Alt(Peek('Required'), DAY), DAY) as Required;

LOAD DAY,

MonthName(DAY) as MonthName,

[US WD]

FROM

Community_164851.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Need help

Tue, 26 May 2015 16:23:21 GMT

U are awesome! Thanks for all your help

Re: Need help

sunny_talwar — Tue, 26 May 2015 16:24:09 GMT

No problem at all

Have a good day.

Best,

Sunny