Need script

[#1]

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).

[flipside]

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.

[jagan]

Hi Reena,

PFA file for solution.

Regards,

jagan.

[jsingh71]

Hi Reena,

Using Crosstable load you can get the result.

Find attachment.

[#5]

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.

[flipside · Accepted solution]

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

[MarcoWedel]

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

[prodanov]

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

[MarcoWedel]

Please close this thread if your question is answered.