topic Re: Urgent help required in solving this problem in QlikView

Urgent help required in solving this problem

fashid — Mon, 07 Apr 2014 18:28:31 GMT

Hi everybody,

I have a requirement in which i have been given an excel file with some columns

I have been told to create a table in which apart from the columns mentioned in the excel there should be column with the name

(no of days)

.I am having problem with calculating this field .

the no of days field is calculated as a difference between the date mentioned in the data field and the sent date field, also the no of days field for sms type = 4 should be calculated on the basis of sent date for sms type= 2

eg for doc no :-D02 no of days field should be 04/04/2014 - 01/04/2014 where sms type =2

D02 no of days field should be 02/04/2014 - 01/04/2014 where sms type = 4

also these calculation should occur only where sms type is 2 and 4 for each doc no , i.e if a particular doc no doesnt have either of these sms types then i should ignore that doc no

I am newbie to qlikview and i think this is a very complex requirement for me .

I am attaching the excel file

Plzzz help

thanks and regards

Re: Urgent help required in solving this problem

its_anandrjs — Mon, 07 Apr 2014 18:41:29 GMT

First you have to filter data like this

LOAD kVPS,

[DOC NO],

[SENT DATE],

[SENT TIME],

[SMS TYPE],

DATA

FROM

(ooxml, embedded labels, table is Sheet1)

Where Match([SMS TYPE],2,4);

and what is the date format of the DATA field because it contains other data also

Re: Urgent help required in solving this problem

MK_QSL — Mon, 07 Apr 2014 19:09:42 GMT

Use below in script...

=============

Temp:

Load

Date(Date#(Mid(DATA,27,10),'DD/MM/YYYY')) as DataDate,

[DOC NO]&[SMS TYPE] as Key,

IF([SMS TYPE] = 2,'Yes','No') as Flag

Inline

[

kVPS, DOC NO, SENT DATE, SENT TIME, SMS TYPE, DATA

13042, d01, 01/04/2014, 10:00, 1, Amount: Rs 15.00 # Date: 03/04/2014 10:00:00

13045, d02, 01/04/2014, 12:00, 2, Amount: Rs 18.00 # Date: 04/04/2014 17:00:00

13045, d02, 02/04/2014, 03:00, 4, Amount: Rs 18.00 # Date: 02/04/2014 09:00:00

12000, d03, 03/04/2014, 12:00, 2, Amount: Rs 13.00 # Date: 05/04/2014 17:00:00

12000, d03, 07/04/2014, 11:00, 4, Amount: Rs 13.00 # Date: 07/04/2014 13:00:00

14560, d04, 02/04/2014, 09:00, 2, Amount: Rs 11.00 # Date: 02/04/2014 14:00:00

14560, d04, 02/04/2014, 10:00, 4, Amount: Rs 11.00 # Date: 02/04/2014 14:00:00

18000, d05, 03/04/2014, 04:00, 2, Amount: Rs 12.00 # Date: 04/04/2014 15:00:00

18000, d05, 06/04/2014, 08:00, 4, Amount: Rs 12.00 # Date: 06/04/2014 12:00:00

20000, d06, 07/04/2014, 11:00, 4, Amount: Rs 10.00 # Date: 07/04/2014 13:00:00

];

NoConcatenate

Temp2:

Load

kVPS,

[DOC NO],

[SENT DATE],

[SENT TIME],

[SMS TYPE],

DATA,

IF([SMS TYPE] = 2,DataDate - [SENT DATE], DataDate-Peek('SENT DATE')) as Difference

Resident Temp

Where Flag = 'Yes' or Left(Key,3) = Left(Previous(Key),3)

==================================

File enclosed for your reference...

Order By [DOC NO], [SMS TYPE];

Drop Table Temp;

===============

Update : If you have [SMS TYPE] other than 1, 2 and 4 also, use second file....

where slight change in second table...

NoConcatenate

Temp2:

Load

kVPS,

[DOC NO],

[SENT DATE],

[SENT TIME],

[SMS TYPE],

DATA,

IF([SMS TYPE] = 2,DataDate - [SENT DATE], DataDate-Peek('SENT DATE')) as Difference

Resident Temp

Where Flag = 'Yes' or Left(Key,3) = Left(Previous(Key),3) and Right(Key,1)-2 = Right(Previous(Key),1) and Right(Key,1)=4

Order By [DOC NO], [SMS TYPE];

Re: Urgent help required in solving this problem

Anonymous — Mon, 07 Apr 2014 19:24:50 GMT

See attached

Re: Urgent help required in solving this problem

Mon, 07 Apr 2014 19:26:29 GMT

Hi,

try the below script

Task60:

LOAD kVPS,

[DOC NO],

[SENT DATE],

[SENT TIME],

[SMS TYPE],

DATA,

left( mid(DATA,27),10) as DATA_new

FROM

(ooxml, embedded labels, table is Sheet1)

where [SMS TYPE] = 2 or [SMS TYPE] =4;

Task60_1:

load

kVPS,

[DOC NO],

[SENT DATE],

[SENT TIME],

[SMS TYPE],

DATA,

DATA_new,

date(DATA_new,'dd/mm/yyyy')-date([SENT DATE],'dd/mm/yyyy') as [no of days]

resident Task60;

drop table Task60;

Re: Urgent help required in solving this problem

maksim_senin — Mon, 07 Apr 2014 22:00:05 GMT

Personally I strongly recommend start (re-)reading QV documentation and have a QV dev. training if possible whilst you're waiting an answer at the forum.

Re: Urgent help required in solving this problem

fashid — Fri, 11 Apr 2014 10:11:40 GMT

Thanks a Million perfect Solution .

Re: Urgent help required in solving this problem

fashid — Fri, 11 Apr 2014 10:20:24 GMT

Hii,

Your Solution was perfect but after implementing that i was faced with a different situation wherein i had to choose manisha's Solution

By the way i thank you for taking your time and helping me

Warm Regards,

Nadeem Shaikh