Flag creation in data load script - duplicates - Qlik Community

nnandapalan · ‎2019-09-30

Hi, when i try to create a derived field to be a flag based on whether a completion date is entered - i get duplicate data rows.

Could i please be helped?

My current load script (EDIT: updated with full load script) is

Set dataManagerTables = '','Product Review','(Product Review Outcome...)','Product Review Outcome','Product Review Outcome View','(Product Review Task...)','Product Review Task','Product Review Task View','(Product Review Task Reference...)','Product Review Task Reference','Product Review Task Reference View','Product Review Licence','Product Review ATC View','Licence','Product Specific Indication';
//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables) 
    Let index = 0;
    Let currentName = name; 
    Let tableNumber = TableNumber(name); 
    Let matches = 0; 
    Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
        index = index + 1; 
        currentName = name & '-' & index; 
        tableNumber = TableNumber(currentName) 
        matches = Match('$(currentName)', $(dataManagerTables));
    Loop 
    If index > 0 then 
            Rename Table '$(name)' to '$(currentName)'; 
    EndIf; 
Next; 
Set dataManagerTables = ;


Unqualify *;

LIB CONNECT TO [HPRG_IDS_PROD];

[Product Review]:
LOAD
	[Product Review Id] AS [Product Review Task.Product Review Id-Product Review.Product Review Id],
	[Review Received Date] AS [Product Review.Review Received Date],
	[Review Manager User Id] AS [Product Review.Review Manager User Id],
	[Review Manager] AS [Product Review.Review Manager],
	Date([Review Start Date] ) AS [Product Review.Review Start Date],
	[Product Type Code] AS [Product Review.Product Type Code],
	[Product Type] AS [Product Review.Product Type],
	[Product Type Sort Order] AS [Product Review.Product Type Sort Order],
	[Registration Type Code] AS [Product Review.Registration Type Code],
	[Registration Type] AS [Product Review.Registration Type],
	[Review GMDN Code] AS [Product Review.Review GMDN Code],
	[Investigation Type Code] AS [Product Review.Product Review.Investigation Type Code],
	[Investigation Type] AS [Product Review.Product Review.Investigation Type],
	[Investigation Type Sort Order] AS [Product Review.Product Review.Investigation Type Sort Order],
	[Review Priority Code] AS [Product Review.Review Priority Code],
	[Review Priority] AS [Product Review.Review Priority],
	[Review Priority Sort Order] AS [Product Review.Review Priority Sort Order],
	[Review Product Description] AS [Product Review.Review Product Description],
	[Ingredient Of Concern] AS [Product Review.Ingredient Of Concern],
	[Review Body Type Code] AS [Product Review.Review Body Type Code],
	[Review Body Type] AS [Product Review.Review Body Type],
	[Review Body Type Sort Order] AS [Product Review.Review Body Type Sort Order],
	[Review Body Name] AS [Product Review.Review Body Name],
	[Review Body Contact] AS [Product Review.Review Body Contact],
	[Review Body Fax] AS [Product Review.Review Body Fax],
	[Review Body Phone] AS [Product Review.Review Body Phone],
	[Review Body Address Line1] AS [Product Review.Review Body Address Line1],
	[Review Body Address Line2] AS [Product Review.Review Body Address Line2],
	[Review Body Town] AS [Product Review.Review Body Town],
	[Review Body State] AS [Product Review.Review Body State],
	[Review Body Postcode] AS [Product Review.Review Body Postcode],
	[Issue Context] AS [Product Review.Issue Context],
	[Review TGA Notes] AS [Product Review.Review TGA Notes],
	[Issue Source Code] AS [Product Review.Issue Source Code],
	[Issue Source] AS [Product Review.Issue Source],
	[Issue Source Sort Order] AS [Product Review.Issue Source Sort Order],
	[Source Contact Name] AS [Product Review.Source Contact Name],
	[Source Contact Address] AS [Product Review.Source Contact Address],
	[Source Acknowledged] AS [Product Review.Product Review.Source Acknowledged],
	[Review Status Code] AS [Product Review.Review Status Code],
	[Review Status] AS [Product Review.Review Status],
	[Review Status Sort Order] AS [Product Review.Review Status Sort Order],
	[Review Type Code] AS [Product Review.Review Type Code],
	[Review Type] AS [Product Review.Review Type],
	[Review Type Sort Order] AS [Product Review.Review Type Sort Order],
	[Review Finalisation Date] AS [Product Review.Review Finalisation Date],
	[Product Type Class Code] AS [Product Review.Product Type Class Code],
	[Product Type Class] AS [Product Review.Product Type Class],
	[Product Type Class Sort Order] AS [Product Review.Product Type Class Sort Order],
	[RMP Submission Id] AS [Product Review.RMP Submission Id];
SQL SELECT "Product Review Id",
	"Review Received Date",
	"Review Manager User Id",
	"Review Manager",
	"Review Start Date",
	"Product Type Code",
	"Product Type",
	"Product Type Sort Order",
	"Registration Type Code",
	"Registration Type",
	"Review GMDN Code",
	"Investigation Type Code",
	"Investigation Type",
	"Investigation Type Sort Order",
	"Review Priority Code",
	"Review Priority",
	"Review Priority Sort Order",
	"Review Product Description",
	"Ingredient Of Concern",
	"Review Body Type Code",
	"Review Body Type",
	"Review Body Type Sort Order",
	"Review Body Name",
	"Review Body Contact",
	"Review Body Fax",
	"Review Body Phone",
	"Review Body Address Line1",
	"Review Body Address Line2",
	"Review Body Town",
	"Review Body State",
	"Review Body Postcode",
	"Issue Context",
	"Review TGA Notes",
	"Issue Source Code",
	"Issue Source",
	"Issue Source Sort Order",
	"Source Contact Name",
	"Source Contact Address",
	"Source Acknowledged",
	"Review Status Code",
	"Review Status",
	"Review Status Sort Order",
	"Review Type Code",
	"Review Type",
	"Review Type Sort Order",
	"Review Finalisation Date",
	"Product Type Class Code",
	"Product Type Class",
	"Product Type Class Sort Order",
	"RMP Submission Id"
FROM "EpmmaStage"."IDS"."Product Review";

[Product Review Outcome]:
LOAD
	[Product Review Outcome Id],
	[Product Review Id] AS [Product Review Task.Product Review Id-Product Review.Product Review Id],
	[Outcome Code],
	[Outcome],
	[Outcome Comment],
	[Outcome Action Completion Date],
	[Action Body Id] AS [Product Review Outcome.Action Body Id],
	[Action Body Name] AS [Product Review Outcome.Action Body Name],
	[Source Acknowledged] AS [Product Review Outcome.Source Acknowledged],
	[Outcome Reason Code],
	[Outcome Reason];
SQL SELECT "Product Review Outcome Id",
	"Product Review Id",
	"Outcome Code",
	"Outcome",
	"Outcome Comment",
	"Outcome Action Completion Date",
	"Action Body Id",
	"Action Body Name",
	"Source Acknowledged",
	"Outcome Reason Code",
	"Outcome Reason"
FROM "EpmmaStage"."IDS"."Product Review Outcome";

LOAD
	[Product Review Outcome Id],
	[Product Review Id] AS [Product Review Task.Product Review Id-Product Review.Product Review Id],
	[Outcome Code],
	[Outcome],
	[Outcome Comment],
	[Outcome Action Completion Date],
	[Action Body Id] AS [Product Review Outcome.Action Body Id],
	[Action Body Name] AS [Product Review Outcome.Action Body Name],
	[Source Acknowledged] AS [Product Review Outcome.Source Acknowledged],
	[Outcome Reason Code],
	[Outcome Reason];
SQL SELECT "Product Review Outcome Id",
	"Product Review Id",
	"Outcome Code",
	"Outcome",
	"Outcome Comment",
	"Outcome Action Completion Date",
	"Action Body Id",
	"Action Body Name",
	"Source Acknowledged",
	"Outcome Reason Code",
	"Outcome Reason"
FROM "EpmmaStage"."IDS"."Product Review Outcome View";



LIB CONNECT TO [HPRG_IDS_PROD];

[Product Review Task]:

//Load - this doesnt work
//	if(isnull([Task Complete Date]),'uncompleted','completed') as [Task Status TEST];

LOAD
	[Product Review Task Id] AS [Product Review Task Reference Id-Product Review Task.Product Review Task Id],
	[Product Review Id] AS [Product Review Task.Product Review Id-Product Review.Product Review Id],
	[Task Activity Code],
	[Task Activity],
	[Task],
	[Task Response],
	Date([Task Start Date] ) AS [Task Start Date],
	[Response Due Date],
	[Response Received Date],
	[Action Body Id] AS [Product Review Task.Action Body Id],
	[Action Body Name] AS [Product Review Task.Action Body Name],
	[Investigation Type Code] AS [Product Review Task.Investigation Type Code],
	[Investigation Type] AS [Product Review Task.Investigation Type],
	[Investigation Type Sort Order] AS [Product Review Task.Investigation Type Sort Order],
	[Task Outcome Code],
	[Task Outcome],
	[Task Outcome Sort Order],
	Date([Task Complete Date] ) AS [Task Complete Date],
	[Assigned Evaluator User Id],
	[Assigned Evaluator];
SQL SELECT "Product Review Task Id",
	"Product Review Id",
	"Task Activity Code",
	"Task Activity",
	"Task",
	"Task Response",
	"Task Start Date",
	"Response Due Date",
	"Response Received Date",
	"Action Body Id",
	"Action Body Name",
	"Investigation Type Code",
	"Investigation Type",
	"Investigation Type Sort Order",
	"Task Outcome Code",
	"Task Outcome",
	"Task Outcome Sort Order",
	"Task Complete Date",
	"Assigned Evaluator User Id",
	"Assigned Evaluator"
FROM "EpmmaStage"."IDS"."Product Review Task";

LOAD
	[Product Review Task Id] AS [Product Review Task Reference Id-Product Review Task.Product Review Task Id],
	[Product Review Id] AS [Product Review Task.Product Review Id-Product Review.Product Review Id],
	[Task Activity Code],
	[Task Activity],
	[Task],
	[Task Response],
	Date([Task Start Date] ) AS [Task Start Date],
	[Response Due Date],
	[Response Received Date],
	[Action Body Id] AS [Product Review Task.Action Body Id],
	[Action Body Name] AS [Product Review Task.Action Body Name],
	[Investigation Type Code] AS [Product Review Task.Investigation Type Code],
	[Investigation Type] AS [Product Review Task.Investigation Type],
	[Investigation Type Sort Order] AS [Product Review Task.Investigation Type Sort Order],
	[Task Outcome Code],
	[Task Outcome],
	[Task Outcome Sort Order],
	Date([Task Complete Date] ) AS [Task Complete Date], 
	[Assigned Evaluator User Id],
	[Assigned Evaluator];
SQL SELECT "Product Review Task Id",
	"Product Review Id",
	"Task Activity Code",
	"Task Activity",
	"Task",
	"Task Response",
	"Task Start Date",
	"Response Due Date",
	"Response Received Date",
	"Action Body Id",
	"Action Body Name",
	"Investigation Type Code",
	"Investigation Type",
	"Investigation Type Sort Order",
	"Task Outcome Code",
	"Task Outcome",
	"Task Outcome Sort Order",
	"Task Complete Date",
	"Assigned Evaluator User Id",
	"Assigned Evaluator"
FROM "EpmmaStage"."IDS"."Product Review Task View";

LIB CONNECT TO [HPRG_IDS_PROD];

[Product Review Task Reference]:
LOAD
	[Product Review Task Reference Id] AS [Product Review Task Reference Id-Product Review Task.Product Review Task Id],
	[Product Review Task Id] AS [Product Review Task Reference.Product Review Task Id],
	[Reference Type Code],
	[Reference Type],
	[Reference];
SQL SELECT "Product Review Task Reference Id",
	"Product Review Task Id",
	"Reference Type Code",
	"Reference Type",
	"Reference"
FROM "EpmmaStage"."IDS"."Product Review Task Reference";

LOAD
	[Product Review Task Reference Id] AS [Product Review Task Reference Id-Product Review Task.Product Review Task Id],
	[Product Review Task Id] AS [Product Review Task Reference.Product Review Task Id],
	[Reference Type Code],
	[Reference Type],
	[Reference];
SQL SELECT "Product Review Task Reference Id",
	"Product Review Task Id",
	"Reference Type Code",
	"Reference Type",
	"Reference"
FROM "EpmmaStage"."IDS"."Product Review Task Reference View";




RENAME TABLE [Product Review Outcome] to [(Product Review Outcome...)];

RENAME TABLE [Product Review Task] to [(Product Review Task...)];

RENAME TABLE [Product Review Task Reference] to [(Product Review Task Reference...)];


[autoCalendar]: 
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
  Month($1) AS [Month] Tagged ('$month', '$cyclic'),
  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
  Year(Today())-Year($1) AS [YearsAgo] ,
  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
  Month(Today())-Month($1) AS [MonthRelNo] ,
  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Product Review.Review Received Date], [Product Review.Review Start Date], [Product Review.Product Review.Source Acknowledged], [Product Review.Review Finalisation Date], [Outcome Action Completion Date], [Product Review Outcome.Source Acknowledged], [Task Start Date], [Response Due Date], [Response Received Date], [Task Complete Date], [Licence Start Date], [Licence Cancellation Date], [Creation Date], [Last Update Date] USING [autoCalendar] ;

NZFei · ‎2019-09-30

if you only do load on load, it shouldn't create duplicated rows.

Did you try to join this flag to other tables? Can you post the full script?

nnandapalan · ‎2019-09-30

Thank you for your response. The table is joined to other tables, but I do not have any synthetic keys. I have put the full code original post

NZFei · ‎2019-09-30

You know for the following script, the whole table only returns one field [Task Status Test] and the value in that field is either 'uncompleted' or 'completed'. And if you use DISTINCT, there will be only two lines.

What is the point to load so many fields while your top load script only define the field [Task Status Test] based on one field [Task Complete Date]?

nnandapalan · ‎2019-10-01

Thanks for your response mate. How do i load all the fields in table 'Product Review Task' and the derived field 'Task status TEST'? This app is supposed to load 4 associated tables which are the relational database behind and old (and complex) workflow app, all fields are in use for viz. You suggested adding a distinct, where should the 'distinct' go (in load script or the viz?)

Flag creation in data load script - duplicates

create flag load editor