topic Re: Need script in QlikView

Need script

Thu, 20 Mar 2014 07:42:27 GMT

Dear All,

Greetings for the day.

I have a requirement as below. Please provide me the solution for the same. Thanks in advance.

REQUIREMENT :-

In our data base we have a table with fields as below. This is just a sample data (SAMPLE DATA BOX) in the table. In actual table, we have many fields and records. If ETD date is "99999999" means then there is no etd date given by user, so by default system will write "99999999" in database. I want to create a table as shown below (REQUIREMENT BOX).

Re: Need script

flipside — Thu, 20 Mar 2014 08:54:02 GMT

Hi Reena,

One way is to create a straight table chart with Part No., Cust Code and Order No dimensions, then your first expression as ...

=concat(ETD Date,',',ETD Date)

You can then create expressions based on the first one, so if you labelled it ETDList, you can put ...

=subfield([ETDList],',',1)

... and so on for each date needed.

flipside

EDIT: =firstsortedvalue(ETD,ETD,1) will also work without needing the concat, where the last value is the ranking.

Re: Need script

jagan — Thu, 20 Mar 2014 09:25:10 GMT

Hi Reena,

PFA file for solution.

Regards,

jagan.

Re: Need script

jsingh71 — Thu, 20 Mar 2014 09:26:51 GMT

Hi Reena,

Using Crosstable load you can get the result.

Find attachment.

Re: Need script

Thu, 27 Mar 2014 09:58:26 GMT

Hello san,

Greetings for the day. Sorry for late rsponse.

Thanks for solution. As per my sample data, I am getting the answer correctly. But if my sample data is as follows, please let me know how to solve. Thank you.

Re: Need script

flipside — Thu, 27 Mar 2014 12:17:19 GMT

Hi,

You will first need to 'mark' the data with the row reference number like this ...

Load PartNo, CustCode, OrderNo, Dual(ETD,RowNo()) as ETD inline [
PartNo, CustCode, OrderNo, ETD
123, A, A123, 99999999
123, A, A123, 16/03/2014 ...

... then change your expressions to these ...

firstsortedvalue({<ETD={'*'}-{99999999}>} ETD,ETD,1)

firstsortedvalue({<ETD={'*'}-{99999999}>} ETD,ETD,2)

firstsortedvalue({<ETD={'*'}-{99999999}>} ETD,ETD,3)

firstsortedvalue({<ETD={'*'}-{99999999}>} ETD,ETD,4)

firstsortedvalue({<ETD={99999999}>} ETD,ETD,1)

Hope this helps

flipside

Re: Need script

MarcoWedel — Thu, 27 Mar 2014 17:14:35 GMT

Hi Reena,

if you want to create a table in your data model like you described in your post, then this might be a solution:

SET DateFormat='DD.MM.YYYY';

tabInput:
LOAD * INLINE [
    PartNo, CustCode, OrderNo, ETDDate
    123, A, A123, 99999999
    123, A, A123, 15.03.2014
    123, A, A123, 16.03.2014
    123, A, A123, 17.03.2014
    123, A, A123, 18.03.2014
    456, B, B456, 16.03.2014
    456, B, B456, 17.03.2014
    456, B, B456, 18.03.2014
];

tabTemp:
LOAD
    *,
    If(ETDDate = 99999999, 'NOETD', 'ETD' & ETDNum) AS ETDNumName;
LOAD
    PartNo,
    [CustCode],
    [OrderNo],
    ETDDate,
    If(Previous(PartNo&'/'&CustCode&'/'&OrderNo)=PartNo&'/'&CustCode&'/'&OrderNo, Peek(ETDNum) + 1, 1) AS ETDNum
RESIDENT tabInput
ORDER BY PartNo, [CustCode], [OrderNo], ETDDate;

DROP Field ETDNum;
//DROP Table tabInput;

tabTemp2:
Generic LOAD
    PartNo,
    [CustCode],
    [OrderNo],
    ETDNumName,
    ETDDate
RESIDENT tabTemp;

tabOutput:
LOAD Distinct
    PartNo,
    [CustCode],
    [OrderNo]
Resident tabTemp;

DROP Table tabTemp;

FOR i = 0 to NoOfTables()
  TableList:
  LOAD TableName($(i)) as Tablename AUTOGENERATE 1
  WHERE WildMatch(TableName($(i)), 'tabTemp2.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
  LET vTable = FieldValue('Tablename', $(i));
  LEFT JOIN (tabOutput) LOAD * RESIDENT $(vTable);
  DROP TABLE $(vTable);
NEXT i

DROP TABLE TableList;

This script uses a generic load to create the ETDx fileds and a technique described by Rob Wunderlich to combine the generated tables into one: (Qlikview Notes: Use cases for Generic Load)

Uncomment the drop tabInput line to get only the output table.

hope this helps

regards

Marco

Re: Need script

prodanov — Fri, 28 Mar 2014 09:31:37 GMT

Do you find a solution for this case?

And what exactly do you want to do! To order data in table in Data Model or present data in some layout report?

Regards,

Dimitar

Re: Need script

MarcoWedel — Thu, 24 Apr 2014 08:41:18 GMT

Please close this thread if your question is answered.