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: 
harleen_singh
Creator III
Creator III

data model suggestion

Hello,

            i have a fact table in the middle with a Date field and Date field is linked with Ca lander.

I have following fields in Fact table. So here date field is linked with Master Calender

Rno

Date,

Quantity

Now what i want a Flag in a data model where Quantity is greater than 744 in a month for particular RNo. But, if i do group by monthyear than my data model will convert into monthyear level which i don't want. I want to keep my data model into Date level.

What should i do in this case?

thanks

Lavi

1 Solution

Accepted Solutions
tresesco
MVP
MVP

You can try something like:

TempFact:

Load

          no

          Date,

          Quantity,

          Month(Date) as Month

From <'>;

Left Join

Load Month,

          If(Sum(Quantity)>744, 'Y', 'N') as Flag

Resident TempFact Group By Month;

View solution in original post

6 Replies
tresesco
MVP
MVP

You can try something like:

TempFact:

Load

          no

          Date,

          Quantity,

          Month(Date) as Month

From <'>;

Left Join

Load Month,

          If(Sum(Quantity)>744, 'Y', 'N') as Flag

Resident TempFact Group By Month;

jagan
Luminary Alumni
Luminary Alumni

HI,

Try like this

Data:

LOAD

*,

MonthName(Date) AS Month

FROM Datasource;

GroupData:

LOAD

Month,

If(Sum(Quantity)>744, 1, 0) as Flag

RESIDENT Data

GROUP BY Month;


If you want to group by RNo also then try this


GroupData:

LOAD

Month,

RNo,

If(Sum(Quantity)>744, 1, 0) as Flag

RESIDENT Data

GROUP BY Month, RNo;


Hope this helps you.


Regards,

Jagan.

harleen_singh
Creator III
Creator III
Author

@jagan you are correct but as i said. if i do this my "FACT"Table will convert into Month year Level?

I also want to keep it into Date level so that i can also see daily details.

thanks

lavi

jagan
Luminary Alumni
Luminary Alumni

HI,

Here we are not changing the Fact Table, you fact table stays as such, we are not joining the tables and we are just arriving the Flag field in the new table.  The new table just has a flag with Month/RNo.

Regards,

Jagan.

Anonymous
Not applicable

I think tresesco's answer might be best fit for your requirement! Because, it will keep your data model on date level. It just adds extra fields Month and flag to the existing fact table. And this is what you are supposed to ask right!

harleen_singh
Creator III
Creator III
Author

right Vamshi. If you are correct i just need to apply "Left Join" with the fact Table

thanks

Lavi