Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

aveeeeeee7en
Not applicable

WeekDays Logic

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:

Originall111111.png

Required Data:

Requireddddddd1111.png

PFA.

Regards

Av7eN

Tags (1)
1 Solution

Accepted Solutions
jagan
Not applicable

Re: WeekDays Logic

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.

11 Replies
alkesh_sharma
Not applicable

Re: WeekDays Logic

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.

preminqlik
Not applicable

Re: WeekDays Logic

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;

aveeeeeee7en
Not applicable

Re: WeekDays Logic

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;

jagan
Not applicable

Re: WeekDays Logic

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.

aveeeeeee7en
Not applicable

Re: WeekDays Logic

Jagan Sir

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

I am getting the desired result.

jagan
Not applicable

Re: WeekDays Logic

Hi,

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

Regards,

Jagan.

aveeeeeee7en
Not applicable

Re: WeekDays Logic

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.

jagan
Not applicable

Re: WeekDays Logic

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.

tresesco
Not applicable

Re: WeekDays Logic

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.