Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aveeeeeee7en
Specialist III
Specialist III

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

11 Replies
alkesh_sharma
Creator III
Creator III

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
Specialist II
Specialist II

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
Specialist III
Specialist III
Author

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
Luminary Alumni
Luminary Alumni

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
Specialist III
Specialist III
Author

Jagan Sir

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

I am getting the desired result.

jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

aveeeeeee7en
Specialist III
Specialist III
Author

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
Luminary Alumni
Luminary Alumni

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
MVP
MVP

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.