topic WeekDays Logic in QlikView

WeekDays Logic

aveeeeeee7en — Thu, 16 Oct 2014 04:43:31 GMT

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

Re: WeekDays Logic

alkesh_sharma — Thu, 16 Oct 2014 05:05:27 GMT

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.

Re: WeekDays Logic

preminqlik — Thu, 16 Oct 2014 05:34:56 GMT

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;

Re: WeekDays Logic

aveeeeeee7en — Thu, 16 Oct 2014 05:37:20 GMT

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;

Re: WeekDays Logic

jagan — Thu, 16 Oct 2014 05:44:01 GMT

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.

Re: WeekDays Logic

aveeeeeee7en — Thu, 16 Oct 2014 05:47:09 GMT

Jagan Sir

Is my Approach correct or it need to go through some changes.

I am getting the desired result.

Re: WeekDays Logic

jagan — Thu, 16 Oct 2014 05:47:54 GMT

Hi,

Your code will give you duplicate records for records which you already have Weekdays.

Regards,

Jagan.

Re: WeekDays Logic

aveeeeeee7en — Thu, 16 Oct 2014 05:53:27 GMT

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.

Re: WeekDays Logic

jagan — Thu, 16 Oct 2014 05:56:25 GMT

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.

Re: WeekDays Logic

tresB — Thu, 16 Oct 2014 06:05:36 GMT

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.

Re: WeekDays Logic

preminqlik — Thu, 16 Oct 2014 07:39:23 GMT

hi try below for Little more simpler without loops and concatenation

Load *,

if(len(trim(WeekDays))=0,if(Flag='IDC',SubField('Mon-Tue-Wed-Thu-Fri-Sat','-'),SubField('Mon-Tue-Wed-Thu-Fri','-')),WeekDays) as FINALWEEKDAYS

from PATH;

Re: WeekDays Logic

aveeeeeee7en — Thu, 16 Oct 2014 07:56:12 GMT

Prem & Tresesco Sir I like both of yours Approach.

Great Work of Mind.

Special Thanks to Jagan Mohan Sir for identifying duplicates in my Logic.

I was in so hurry that I missed that part of Duplicate Recoords. Later learnt that I was wrong.

Thank you All for providing such wonderful solutions and that too instantly.

Reagards

Av7eN