Forum Discussion

nok's avatar
nok
Icon for Advocate II rankAdvocate II
2 years ago
Solved

Repeat each date from a Calendar table for each different ID

Hello!

I have a Calendar table that shows a row for each day from 1/1/2023 to today's date like that:

Date
1/1/2023
2/1/2023
3/1/2023
...
2/9/2024

 

I want to modify this table to repeat each date for each different Category coming from another table called "Ref_Category, which looks like this:

CategoryName
Paper
Pen
Pencil

 

The expected end result of my new "Calendar" table would be something like this:

DateCategoryName
1/1/2023   Paper
2/1/2023Paper
3/1/2023Paper
...Paper
2/9/2024Paper
1/1/2023Pen
2/1/2023Pen
3/1/2023Pen
...Pen
2/9/2024Pen
1/1/2023Pencil
2/1/2023Pencil
3/1/2023Pencil
...Pencil
2/9/2024Pencil

 

Is it possible to do this?

Need Help
Show and Tell
Tutorial Requests
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi nok 

    Do you want this as a new table in the Data Model or a table visual in the front end?

    A visual would look like this:

    Put the Date column and the Category column in the visual.

     

    The actual Data Table would use Cross Join to combine the two tables. On the Modeling tab choose New Table.

    SelectColumns lets you choose which columns to include in the cross join:

     

     

     

1 Reply

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    Hi nok 

    Do you want this as a new table in the Data Model or a table visual in the front end?

    A visual would look like this:

    Put the Date column and the Category column in the visual.

     

    The actual Data Table would use Cross Join to combine the two tables. On the Modeling tab choose New Table.

    SelectColumns lets you choose which columns to include in the cross join: