If condition Help Required

[pra_kale]

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

A:

LOAD Empno,

     Datepass,

     Month(Datepass) as Month_Datepass,

     Year(Datepass) as Year_Datepass,

     Result

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

B:

LOAD *,

Month_Datepass &'-' & Year_Datepass as Month_Year

Resident A;

DROP Table A;

NoConcatenate

C:

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;

[Kushal_Chawda]

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;

[#3]

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.

[pra_kale]

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

[Kushal_Chawda · Accepted solution]

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;

[pra_kale]

Great Kush...now working perfectly.

[Kushal_Chawda]

Awsome

[pra_kale]

Thanks for pointing..

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