Creating a flag based on the missing period values

[#1]

Hi folks,

I need to create a flag in the fact table wherever periods are missing.

Scenario is.

I've a fact table with different countries,years,periods,flag and value. Wherever Period is missing for a country, year I need to populate the flag with zero.

Example:

if period p02 data doesn't exist for the country US in 2013, I should flag the country US with Year 2013 as 1, 0 for the rest of selections.

Any idea how this could be achieved?

Thanks,

Surendra

[Gysbert_Wassenaar]

Something like this:

Load

    country,

     year,

     period,

     if(len(trim(period))=0,0,1) as flag,

    value

From ...source...;