how to get the id

[#1]

Hi All,

I have the following table:

YearMonth, Date, TimeId,

2015-1-1,2015-1-1,   100

2015-1-1,2015-1-2,  101

2015-1-1,2015-1-3,  102

I want to use Load script to add one more column for it: get the TimeID of the first day of the current month

For this example, that time id should be 100 for each row, how can I achieve it?

Thanks.

[PrashantSangle]

Hi,

try firstsortedValue(TimeId,YearMonth)

Regards

[qlikmsg4u]

Try this script,

Table1:

load * Inline [

YearMonth, Date, TimeId

2015-1-1,2015-1-1,   100

2015-1-1,2015-1-2,  101

2015-1-1,2015-1-3,  102

2015-2-1,2015-2-4,103

2015-2-1,2015-2-24,104];

Temp:

Mapping load Distinct YearMonth,TimeId Resident Table1;

Load *,ApplyMap('Temp',YearMonth,'Unknown') as New_TimeId Resident Table1;

Drop Table Table1;

[tresesco]

Input:
Load
Date#(Date, 'YYYY-M-D') as Date,
MonthName(Date#(Date, 'YYYY-M-D')) as YearMonth,
TimeId
Inline [
Date, TimeId,
2015-1-1,  100
2015-1-2,  101
2015-1-3,  102
2015-2-4,  104
2015-2-5,  105
2015-2-6,  106
];

Join

Load YearMonth,
  FirstSortedValue(TimeId,Date) as FirstIDofTheMonth
Resident Input Group by YearMonth;

PFA

[prashantbaste]

Hi

Try this:

MainTable:

Load YearMonth,

      Date,

      TimeId

From table....

join(MainTable)

Load YearMonth,

      Min(TimeId) as NewCol

Resident MainTable

Group By YearMonth;

Hope this will be helpful for you.

--

Regards,

Prashant P Baste