topic Re: how to get the id in QlikView

how to get the id

Mon, 20 Jul 2015 06:19:41 GMT

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.

Re: how to get the id

PrashantSangle — Mon, 20 Jul 2015 06:27:13 GMT

Hi,

try firstsortedValue(TimeId,YearMonth)

Regards

Re: how to get the id

qlikmsg4u — Mon, 20 Jul 2015 06:39:58 GMT

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;

Re: how to get the id

tresB — Mon, 20 Jul 2015 07:02:04 GMT

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

Re: how to get the id

prashantbaste — Mon, 20 Jul 2015 07:30:47 GMT

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