topic Re: If condition Help Required in QlikView

If condition Help Required

pra_kale — Sat, 02 Apr 2016 06:02:33 GMT

Hi,

I am attaching one sample application in which i have used If condition to derived batches on the basis of DateofPass.

Because of large data size, to check the dates and put IF condition only on the specific months and year is almost difficult so what I did is i have created Batches on the basis of all the months and Year combination.

As I hard-coded the logic in straight table i am getting batches like Batch 1, Batch 3, Batch 4. As in data i may not get all the months and Year. So whether it possible i will get Batches in order like Batch 1, Batch 2 and Batch 3 and so on though any month is missed out.

Below given is the logic i have used...in data not a single employee is passed out in the month May-2015 and Jul-2015 so ideally next batch should be Batch 2 which is June-2015 and Aug-2015 batch should be Batch-3.

Pl help to fix the logic

Thanks in Advance

LOAD Empno,

Datepass,

Month(Datepass) as Month_Datepass,

Year(Datepass) as Year_Datepass,

Result

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

LOAD *,

Month_Datepass &'-' & Year_Datepass as Month_Year

Resident A;

DROP Table A;

NoConcatenate

LOAD *,

if(Month_Year='Apr-2015','Batch 1',if(Month_Year='May-2015','Batch 2',if(Month_Year='Jun-2015','Batch 3',

if(Month_Year='Jul-2015','Batch 4',if(Month_Year='Aug-2015','Batch 4',if(Month_Year='Sep-2015','Batch 5')))))) as Batches

Resident B;

DROP Table B;

Re: If condition Help Required

Kushal_Chawda — Sat, 02 Apr 2016 06:31:04 GMT

You can create the calendar to achieve the same. Using Min and Max Date you can create the calendar with all the months between min and max date. Link your calendar to Actual Data. Use autonumber function in calendar to generate the batches.

A:
LOAD Empno,
Datepass,
Result
FROM
[batches.xlsx]
(ooxml, embedded labels, table is Sheet1);

MaxMinDate:
LOAD min(Datepass) as MinDate,
max(Datepass) as MaxDate
Resident A;

let vMaxDate = Peek('MaxDate',0,'MaxMinDate');
let vMinDate = Peek('MinDate',0,'MaxMinDate');

Cal:
LOAD *,
year(Datepass) as Year,
month(Datepass) as Month,
monthname(Datepass) as MonthYear;
LOAD date($(vMinDate) +IterNo()-1) as Datepass
AutoGenerate 1
While $(vMinDate) +IterNo()-1<=$(vMaxDate);

Calendar:
LOAD *,
'Batch ' & AutoNumber(MonthYear) as Batches
Resident Cal
Order by MonthYear asc;

DROP Table Cal;

Re: If condition Help Required

Sat, 02 Apr 2016 06:35:56 GMT

Hi Pra_kale,

Here i have one doubt in your "if condition" you done like

if(Month_Year='Jul-2015','Batch 4',if(Month_Year='Aug-2015','Batch 4'

why u Mentioned 2 times " Batch 4" with different months like Jul-2015 & Aug-2015 u have any reason.

Re: If condition Help Required

pra_kale — Sat, 02 Apr 2016 07:01:15 GMT

Hi,

Thanks Kush,

But, what I want is batches should reflects like April-15 as Batch 1; June-15 as Batch 2 ; Aug-15 as Batch 3.

as there are no employees who appeared for exam in May-15 and Jul-15 so naturally next batch i.e. June-15 is second batch and so on

Re: If condition Help Required

Kushal_Chawda — Sat, 02 Apr 2016 07:08:27 GMT

ok then like this

A:
LOAD Empno,
Datepass,
Date(monthstart(Datepass),'MMM-YYYY') as MonthYear,
Result
FROM
[batches.xlsx]
(ooxml, embedded labels, table is Sheet1);

B:
NoConcatenate
LOAD *,
'Batch '& AutoNumber(MonthYear) as Batches
Resident A
Order by MonthYear asc;

DROP Table A;

Re: If condition Help Required

pra_kale — Sat, 02 Apr 2016 07:17:23 GMT

Great Kush...now working perfectly.

Re: If condition Help Required

Kushal_Chawda — Sat, 02 Apr 2016 07:19:31 GMT

Awsome

Re: If condition Help Required

pra_kale — Sat, 02 Apr 2016 09:05:18 GMT

Thanks for pointing..

By mistake it happened actually code should by Batch-4, Batch-5 and so on..