Solved: Re: Urgent help required in solving this problem - Qlik Community

fashid · ‎2014-04-07

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

MK_QSL · ‎2014-04-07

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

View solution in original post

its_anandrjs · ‎2014-04-07

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

MK_QSL · ‎2014-04-07