Hi Satish,
Can you help me in logical help. i have following requirement
,we have a requirement as follows:
1.BHP should be included for the Keywords WCONHIST
2.For each stated keyword the BHP should be included in the table.
WCONHIST – Item 10* = BHP
3. The BHP shall be included only if the value is defined and above a threshold value of 10. Otherwise it shall be stated as “1*”.
Any help will be highly appreciated.Below is the script.
// ******** TXT_EXPORT - WCONHIST ********
//
// Autor: Felix Schmidt
//
//
// LOG:
// Datum Version Änderung
// 24.07.2014 0.29 Erstellt
// 25.07.2014 0.30 TXT_EXPORT.SPEICHER und TXT_EXPORT.BOHRUNG: Resident WCONHIST_tmp
//
// ******** TXT_EXPORT - WCONHIST *******
TRACE WCONHIST;
WCONHIST_tmp:
LOAD Distinct
DateField as WCONHIST_DATE
Resident DateField_tmp;
Left Join(WCONHIST_tmp)
LOAD Distinct
SP_ECL_PRO as WCONHIST_1,
SP_PM_PRO as WCONHIST_3,
SP_VPF_PRO as WCONHIST_7,
SP_ID_BOHRUNG as WCONHIST_ID_BOHRUNG,
'WCONHIST' as WCONHIST_BEZ
Resident SP_TXT
Where (Len(SP_ECL_PRO) > 1)
Order By SP_ECL_PRO asc;
Left Join(WCONHIST_tmp)
LOAD Distinct
TW_DL_ID_BOHRUNG as WCONHIST_ID_BOHRUNG,
TW_DL_DATE as WCONHIST_DATE,
TW_DL_NAME_BOHRUNG as WCONHIST_NAME_BOHRUNG,
TW_DL_SPEICHER as WCONHIST_SPEICHER,
TW_DL_GRAT_EXT as WCONHIST_GRAT_EXT,
TW_DL_GRAT_INJ as WCONHIST_GRAT_INJ,
TW_DL_WRAT as WCONHIST_WRAT,
TW_DL_WHP as WCONHIST_WHP
Resident TW_DL;
// *** Count 0 ***
WCONHIST:
LOAD
'0' as tmp_COUNT,
'DATES' as tmp_ARGUMENT,
WCONHIST_DATE as tmp_DATE,
'WCONHIST' as tmp_BEZ
Resident WCONHIST_tmp;
// *** Count 1 ***
Concatenate(WCONHIST)
LOAD
'1' as tmp_COUNT,
Day(WCONHIST_DATE) &' '& Chr(39) &
Month(WCONHIST_DATE) & Chr(39) &' '&
Year(WCONHIST_DATE) &' '& Chr(47) as tmp_ARGUMENT,
WCONHIST_DATE as tmp_DATE,
'WCONHIST' as tmp_BEZ
Resident WCONHIST_tmp;
// *** Count 2 ***
Concatenate(WCONHIST)
LOAD
'2' as tmp_COUNT,
'/' as tmp_ARGUMENT,
WCONHIST_DATE as tmp_DATE,
'WCONHIST' as tmp_BEZ
Resident WCONHIST_tmp;
// *** Count 3 ***
Concatenate(WCONHIST)
LOAD
'3' as tmp_COUNT,
'WCONHIST' as tmp_ARGUMENT,
WCONHIST_DATE as tmp_DATE,
'WCONHIST' as tmp_BEZ
Resident WCONHIST_tmp;
// *** Count 5 ***
Concatenate(WCONHIST)
LOAD
'5' as tmp_COUNT,
'/' as tmp_ARGUMENT,
WCONHIST_DATE as tmp_DATE,
'WCONHIST' as tmp_BEZ
Resident WCONHIST_tmp;
// *** TXT_EXPORT.SPEICHER und TXT_EXPORT.BOHRUNG ***
// Den Einträgen Count (0,1,2,3,5) müssen alle Speicher und Bohrungen zugeordnet werden.
// Die Felder "TXT_EXPORT.SPEICHER" und "TXT_EXPORT.BOHRUNG" werden später an der Oberfläche
// nicht visualisiert somit wird bei jeder möglichen Selektion der Speicher immer nur eine
// technische Zeile visualisiert.
Left Join(WCONHIST)
LOAD Distinct
WCONHIST_SPEICHER as tmp_SPEICHER,
WCONHIST_NAME_BOHRUNG as tmp_BOHRUNG
Resident WCONHIST_tmp;
// *** Count 4 ***
Concatenate(WCONHIST)
LOAD Distinct
'4' as tmp_COUNT,
WCONHIST_1 &' '&
If(WCONHIST_3 = 'WRAT', If(WCONHIST_WRAT > 0, 'OPEN','STOP'),
If(WCONHIST_3 = 'GRAT', If(WCONHIST_GRAT_EXT > 0,'OPEN', 'STOP'))) &' '&
WCONHIST_3 &' '&
'1* ' &
If(WCONHIST_3 = 'WRAT', If(IsNum(WCONHIST_WRAT),Num(WCONHIST_WRAT, $(vZahlenformat_NK)), 0), '1*') &' '&
If(WCONHIST_3 = 'GRAT', If(IsNum(WCONHIST_GRAT_EXT), Num(WCONHIST_GRAT_EXT, $(vZahlenformat_NK)), 0), '1*') &' '&
If(not IsNull(WCONHIST_7), WCONHIST_7, '1*') &' '&
'1* ' &
If(IsNum(WCONHIST_WHP), Num(WCONHIST_WHP, $(vZahlenformat_NK)), '1*') &' '& chr(47) as tmp_ARGUMENT,
WCONHIST_1 as tmp_ARGUMENT_01,
If(WCONHIST_3 = 'WRAT', If(WCONHIST_WRAT > 0,'OPEN','STOP'),
If(WCONHIST_3 = 'GRAT', If(WCONHIST_GRAT_EXT > 0,'OPEN', 'STOP'))) as tmp_ARGUMENT_02,
WCONHIST_3 as tmp_ARGUMENT_03,
'1*' as tmp_ARGUMENT_04,
If(WCONHIST_3 = 'WRAT', If(IsNum(WCONHIST_WRAT),Num(WCONHIST_WRAT, $(vZahlenformat_NK)), 0), '1*') as tmp_ARGUMENT_05,
If(WCONHIST_3 = 'GRAT', If(IsNum(WCONHIST_GRAT_EXT), Num(WCONHIST_GRAT_EXT, $(vZahlenformat_NK)), 0), '1*') as tmp_ARGUMENT_06,
If(not IsNull(WCONHIST_7), WCONHIST_7, '1*') as tmp_ARGUMENT_07,
'1*' as tmp_ARGUMENT_08,
If(IsNum(WCONHIST_WHP), Num(WCONHIST_WHP, $(vZahlenformat_NK)), '1*') as tmp_ARGUMENT_09,
chr(47) as tmp_ARGUMENT_10,
WCONHIST_DATE as tmp_DATE,
WCONHIST_SPEICHER as tmp_SPEICHER,
WCONHIST_NAME_BOHRUNG as tmp_BOHRUNG,
WCONHIST_BEZ as tmp_BEZ
Resident WCONHIST_tmp;
// *** WCONHIST ***
TXT_EXPORT:
LOAD Distinct
// Key: CALENDAR
tmp_DATE as %KEY_DATE,
// Key: BOHRUNG
tmp_BOHRUNG as %KEY_BO,
// Key: SPEICHER
tmp_SPEICHER as %KEY_SPEICHER,
// ARGUMENT, einzeln
tmp_ARGUMENT_01 as TXT_EXPORT.ARGUMENT_01,
tmp_ARGUMENT_02 as TXT_EXPORT.ARGUMENT_02,
tmp_ARGUMENT_03 as TXT_EXPORT.ARGUMENT_03,
tmp_ARGUMENT_04 as TXT_EXPORT.ARGUMENT_04,
tmp_ARGUMENT_05 as TXT_EXPORT.ARGUMENT_05,
tmp_ARGUMENT_06 as TXT_EXPORT.ARGUMENT_06,
tmp_ARGUMENT_07 as TXT_EXPORT.ARGUMENT_07,
tmp_ARGUMENT_08 as TXT_EXPORT.ARGUMENT_08,
tmp_ARGUMENT_09 as TXT_EXPORT.ARGUMENT_09,
tmp_ARGUMENT_10 as TXT_EXPORT.ARGUMENT_10,
Null() as TXT_EXPORT.ARGUMENT_11,
Null() as TXT_EXPORT.ARGUMENT_12,
// ARGUMENT, gesamt
tmp_ARGUMENT as TXT_EXPORT.ARGUMENT,
tmp_COUNT as TXT_EXPORT.COUNT,
tmp_DATE as TXT_EXPORT.DATE,
tmp_SPEICHER as TXT_EXPORT.SPEICHER,
tmp_BOHRUNG as TXT_EXPORT.BOHRUNG,
tmp_BEZ as TXT_EXPORT.BEZ
Resident WCONHIST;
// Store WCONHIST
//STORE WCONHIST into [..\Data\TXT\WCONHIST.qvd] (qvd);
// tmp Tabellen entfernen
DROP Tables WCONHIST, WCONHIST_tmp;
Regards
Adarsh
in New to QlikView • Share • Reply • Like (0)
