Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community
I want to insert Weekdays Mon, Tue, Wed, Thu, Wed, Thu, Fri, Sat where Nulls are coming in Weekdays column
Logic to Apply:
1): If Flag='DC' than insert Weekdays Mon, Tue, Wed, Thu, Wed, Thu, Fri
2): If Flag='IDC' than insert Weekdays Mon, Tue, Wed, Thu, Wed, Thu, Fri, Sat
Original Data:
Required Data:
PFA.
Regards
Av7eN
Hi,
Try like this
Data:
LOAD
[Serial No], TicketID, [Creation Time], Flag, [Start Time], [End Time]
FROM ExcelFile
WHERE Len(Trim(WeekDays)) = 0;
LEFT JOIN
LOAD
*
INLINE [
Flag, WeekDays
DC, Mon
DC, Tue
DC, Wed
DC, Thu
DC, Fri
IDC, Mon
IDC, Tue
IDC, Wed
IDC, Thu
IDC, Fri
IDC, Sat];
Concatenate(Data)
LOAD
[Serial No], TicketID, [Creation Time], Flag, [Start Time], [End Time],WeekDays
FROM ExcelFile
WHERE Len(Trim(WeekDays)) > 0;
Hope this helps you.
Regards,
Jagan.
Create Weekdays in the master calender, it will pick the day of the week from the Master calender with corresponding Date,
You just need to add a dimention as weekdays.
hi try this ,
temp:
Load *,RecNo() as RecNo;
LOAD * INLINE [
Serial No., TicketID, Creation Time, Flag, Start Time, End Time, WeekDays
QYX00740, 1256670, 3/12/2014 5:24:05 PM, IDC, 9:30, 18:00,
QYX00742, 1402151, 8/8/2014 4:26:30 PM, DC, 9:30, 18:00,
QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Mon
QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Tue
QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Wed
QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Thu
QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Fri
QYX00744, 1402154, 9/8/2014 5:16:30 PM, DC, 9:00, 17:30, Sat
QYX00742, 1402151, 8/8/2014 4:26:30 PM, DC, 9:00, 17:30, Thu
QYX00742, 1402151, 8/8/2014 4:26:30 PM, DC, 9:00, 17:30, Fri
];
NoConcatenate
FINAL_STAGE1:
Load [Serial No.], TicketID, [Creation Time], Flag, [Start Time], [End Time]
Resident temp where len(trim(WeekDays))=0;
join
LOAD * INLINE [
WeekDays
Mon
Tue
Wed
Thu
Fri
Sat
];
Concatenate
Load *
Resident temp where len(trim(WeekDays))>0;
drop table temp;
NoConcatenate
FINAL_STAGE2:
Load *
Resident FINAL_STAGE1 Order by RecNo;
drop table FINAL_STAGE1;
I have done this using Inline Table.
Table:
Flag,
TicketID,
WeekDays
From ....
Left Join
Load * Inline [
Flag, NewWeekDays
DC, Mon
DC, Tue
DC, Wed
DC, Thu
DC, Fri
IDC, Mon
IDC, Tue
IDC, Wed
IDC, Thu
IDC, Fri
IDC, Sat
];
Load *,
if(len(Trim(WeekDays))=0,NewWeekDays,WeekDays) AS NewWeekDaysLogic
Resident Table;
Drop Table Table;
Hi,
Try like this
Data:
LOAD
[Serial No], TicketID, [Creation Time], Flag, [Start Time], [End Time]
FROM ExcelFile
WHERE Len(Trim(WeekDays)) = 0;
LEFT JOIN
LOAD
*
INLINE [
Flag, WeekDays
DC, Mon
DC, Tue
DC, Wed
DC, Thu
DC, Fri
IDC, Mon
IDC, Tue
IDC, Wed
IDC, Thu
IDC, Fri
IDC, Sat];
Concatenate(Data)
LOAD
[Serial No], TicketID, [Creation Time], Flag, [Start Time], [End Time],WeekDays
FROM ExcelFile
WHERE Len(Trim(WeekDays)) > 0;
Hope this helps you.
Regards,
Jagan.
Jagan Sir
Is my Approach correct or it need to go through some changes.
I am getting the desired result.
Hi,
Your code will give you duplicate records for records which you already have Weekdays.
Regards,
Jagan.
if(len(Trim(WeekDays))=0,NewWeekDays,WeekDays) AS NewWeekDaysLogic
Sir, I have used condition for only those cases where weekdays are not available rest for available cases it will work as it is.
Could you please explain how the duplicates will come.
Hi,
For Serial No. QYX00744 you have 6 records, if you join this records with Inline table it becomes 6 * 6 = 36 records. Check this in your data.
Regards,
Jagan.
A bit simpler:
Load
*,
WeekDay(IterNo()+1) as WeekDay
While If(Flag='IDC',iterno()<=6, IterNo()<=5);
Load * Inline [
"Serial No.", TicketID, "Creation Time", Flag, "Start Time", "End Time"
QYX00740, 1256670, 3/12/2014 5:24:05 PM, IDC, 9:30, 18:00
QYX00742, 1402151, 8/8/2014 4:26:30 PM, DC, 9:30, 18:00
]
Note: You might have to adjust 1,5,6 along with iterno() based on your day settings. For me day 0 is Saturday.